topic Data summary in SAS Programming

Data summary

techlearner986 — Wed, 01 Jan 2014 13:58:12 GMT

Hi Team,

I wish you had a wonderful start to the new year 2014!

I need your help in transforming a SAS table to give me a pivot type result. Basically I need to summarize the input table in the layout of output table. A snapshot of both the tables is shown below. I am able to create the required output in excel by creating a pivot table. However, have no idea abut doing this in SAS itself.

Table - Input
Month	Cat	Amount
Jan	Cat1	£12.00
Jan	Cat2	£31.00
Jan	Cat3	£42.00
Feb	Cat1	£12.00
Feb	Cat2	£52.00
Feb	Cat3	£12.00
Feb	Cat4	£52.00
Feb	Cat5	£12.00
Mar	Cat1	£10.00
Mar	Cat2	£12.00
Mar	Cat3	£2.00
Mar	Cat4	£32.00
Mar	Cat5	£12.00

Table - Output
Cat	Jan	Feb	Mar
Cat1	£12.00	£12.00	£10.00
Cat2	£31.00	£52.00	£12.00
Cat3	£42.00	£12.00	£2.00
Cat4	£0.00	£52.00	£32.00
Cat5	£0.00	£12.00	£12.00

Rgds, tech learner

Re: Data summary

Jagadishkatam — Wed, 01 Jan 2014 15:42:03 GMT

Please try

proc sort data=have;

by cat;

run;

proc transpose data=have out=want(drop=_name_);

by cat;

id month;

var amount;

run;

data want_;

set want;

array cha(3) $ jan feb mar;

do i = 1 to 3;

if cha(i)='' then cha(i)='£0.00';

end;

drop i;

run;

Thanks,

jag

Re: Data summary

techlearner986 — Wed, 01 Jan 2014 17:03:09 GMT

thanks jag,

data want (created by the transpose procedure) does the trick for me. I don't see any difference in data want and want_, null values not converted to 0 but thats not an issue at the moment.

many thanks for your quick help!

Cheers, tech

Re: Data summary

Jagadishkatam — Thu, 02 Jan 2014 01:18:36 GMT

I checked the code again and i see want_ has £0.00 replacing the missing values of want dataset.

Could you please check the output again and let me know if there is any problem in the log.

Thanks,

Jag