SAS Program Question: I have a database with two columns: "Bond
Number" and "Trade Date".  If a bond (say with bond number 200), for
example, traded three times in January, 2003 (say the 2nd, 8th and
14th of January) then the data will look like this:
    Bond Number   Trade Date
    200           20030102
    200           20030108
    200           20030114

Here is the problem.  I want to count how many times in a given month
a bond trades.  In my example the answer would (doing the bond number,
number of trades and yearmonth):  "200  3  200301".  However, some
bonds do NOT trade in some months (but do trade in other months).  How
do i assign a 0 to these bonds for these months for which there are no
trades?  Remember, that the data just identifies dates of trade and
does NOT identify dates for which there are no trades.

thanks!

SELECT COUNT(bond) as BOND, year(trade) as trade_year, month(trade) as
trade_month FROM table Group By Year(trade),month(trade) ORDER BY
trade DESC

/* Just paste this into sas and see if it makes sense */

data bonds;
format bond_num $3. trade_dt $8.;
input bond_num trade_dt;
cards;
200 20030102
200 20030108
200 20030114
200 20030214
200 20030314
200 20030414
200 20030514
200 20030614
200 20030714
200 20030814
200 20030914
200 20031014
200 20031214
400 20030912
400 20030913
400 20030914
500 20041112
500 20041213
run;

/* I think you'll need to build a dataset of all possible months in
order to get those months without trading.  First, get a unique list
of your bond numbers: */

proc sort data=bonds nodupkey out=uniq_bonds(keep=bond_num);
by bond_num;
run;

/* Then build the yrmon's for the date range you're concerned with. 
Set the starting and ending year macro variables at the top with your
date range: */

%let start_yr = 2003;
%let end_yr   = 2004;

data yrmons(keep=yrmon);
format yrmon $6.;
do i = &start_yr to &end_yr;
    yrmon = trim(left(i)) || '01'; output;
    yrmon = trim(left(i)) || '02'; output;
    yrmon = trim(left(i)) || '03'; output;
    yrmon = trim(left(i)) || '04'; output;
    yrmon = trim(left(i)) || '05'; output;
    yrmon = trim(left(i)) || '06'; output;
    yrmon = trim(left(i)) || '07'; output;
    yrmon = trim(left(i)) || '08'; output;
    yrmon = trim(left(i)) || '09'; output;
    yrmon = trim(left(i)) || '10'; output;
    yrmon = trim(left(i)) || '11'; output;
    yrmon = trim(left(i)) || '12'; output;
end;
run;

/* Combine these two datasets to get all possible combinations: */

proc sql;
create table bond_yrmon as
select *
from uniq_bonds, yrmons;
quit;

/* Group your current bonds data to get the counts you currently have: */

proc sql;
create table bond_cnt as
select bond_num, substr(trade_dt,1,6) as yrmon, count(bond_num) as cnt
from bonds
group by bond_num, yrmon
order by bond_num, yrmon;
quit;

/* Then just merge your counts with all the months that don't have
data - setting the count to zero for those months that don't exist: */

data final;
merge bond_cnt(in=x) bond_yrmon(in=y);
by bond_num yrmon;
if x=1 and y=1 then output final;
if x=1 and y=0 then output final;
if x=0 and y=1 then do;
   cnt = 0;
   output final;
end;
run;

/* Hope this helps! */