Data summary

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Data summary

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


Accepted Solutions
Solution
‎01-01-2014 10:42 AM
Trusted Advisor
Posts: 1,137

Re: Data summary

Posted in reply to techlearner986

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

Thanks,
Jag

View solution in original post


All Replies
Solution
‎01-01-2014 10:42 AM
Trusted Advisor
Posts: 1,137

Re: Data summary

Posted in reply to techlearner986

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

Thanks,
Jag
New Contributor
Posts: 3

Re: Data summary

Posted in reply to Jagadishkatam

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

Trusted Advisor
Posts: 1,137

Re: Data summary

Posted in reply to techlearner986

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

Thanks,
Jag
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 188 views
  • 3 likes
  • 2 in conversation