topic Re: Having Clause Aggregate totals in SAS Procedures

Having Clause Aggregate totals

Q1983 — Thu, 26 Mar 2015 15:10:39 GMT

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

Re: Having Clause Aggregate totals

ballardw — Thu, 26 Mar 2015 15:24:18 GMT

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.

Re: Having Clause Aggregate totals

Jagadishkatam — Thu, 26 Mar 2015 15:39:26 GMT

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

Re: Having Clause Aggregate totals

Reeza — Thu, 26 Mar 2015 15:40:53 GMT

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;