Having Clause Aggregate totals

[Q1983]

Variables

MonthYr is a date variable

lbl is a char

cnt is a numeric

data test;

MonthYt=step_compl_dt;

if step_cd='111' then do; lbl='Modification';cnt=1;end;else

if step_cd='222' then do; lbl='Liquidation';cnt=1;end;

format MonthYr monyy7.;

drop ln_no;

run;

Sample Output

MonthYr                        lbl                                  cnt

Feb2014                         Modification                    1

Feb2014                         Modifcation                     1

Mar2014                         Liquidation                         1

Mar2014                         Modifcation                     1

Apr2014                         Modification                    1

May2014                         Liquidation                     1

May2014                         Modification                    1

Jun2014                         Modifcation                     1

The repeating months are based on the ln_no which has been dropped

When I attempt to do an agregate total

proc sql;

create table tes2 as

select MonthYr,lbl,sum(cnt) as cnt1

from test1

group by MonthYr,lbl

having sum(cnt);

quit;

Do you see anything wron with this proc statement.  I am getting repeats of the MonthYr and some totals

I should be getting something like

MonthYr                    Modification   Liquidation

Feb2014                         2

Mar2014                          1                    1             

[ballardw]

I suspect that if you look at the values of MonthYr using a format like MMDDYY10 you will see that they are different days of the month.

You want to make sure they are using the same DAY of month as group by doesn't look at formatted value.

[Jagadishkatam]

Please try,

proc sql;

create table tes2 as

select  count(MonthYr) as cnt ,MonthYr, lbl/*, sum(cnt) as cnt1    */

from have

group by MonthYr,lbl

/*having sum(cnt)*/;

quit;

proc transpose data=tes2 out=trans;

by monthyr;

id lbl;

var cnt;

run;

Thanks,

Jag

[Reeza · Accepted solution]

SQL aggregations doesn't recognize formats on the date, you'll have to actually convert it to monyy7 via a text variable, or put them all as the same date and then run your SQL code.

Use either MonthYT2 or MonthYT3 in your code to get what you want.  I'd recommend MonthYT3 as it will still sort properly, while MonthYT2 will sort alphabetically.

data test;

MonthYt=step_compl_dt;

MonthYT2=put(step_compl_dt, monyy7.);

MonthYT3=intnx('month', step_compl_dt, 0, 'b');

if step_cd='111' then do; lbl='Modification';cnt=1;end;else

if step_cd='222' then do; lbl='Liquidation';cnt=1;end;

format MonthYr monyy7.;

drop ln_no;

run;