Data summary

[techlearner986]

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

[Jagadishkatam · Accepted solution]

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

[techlearner986]

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

[Jagadishkatam]

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