Solved
New Contributor
Posts: 3

# 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
Posts: 1,147

## Re: Data summary

Posted in reply to techlearner986

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

All Replies
Solution
‎01-01-2014 10:42 AM
Posts: 1,147

## Re: Data summary

Posted in reply to techlearner986

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

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

Posts: 1,147

## 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
• 203 views
• 3 likes
• 2 in conversation