Hi I have a table I need to create a process transpose the table looks like this
Date. Code. Code Amt
01/01/16. A. 123
01/01/16. B. 145
01/01/16. A. 55
01/02/16. C. 145
01/02/16. A. 55
01/01/16. B. 234
01/01/16. D. 60
i need it to look like this
date. Code. A. Code b. Code c. Code d
01/01/16. 178. 379. 0. 0
01/02/16 0. 0 145. 60
thank you in advance
Or a mix of the 2 solutions above.
proc tabulate data=have out=have_sum;
class date code;
var Code_Amt;
table date,code*sum=''*Code_Amt='' ;
run;
proc transpose data=have_sum out=want(drop=_name_) prefix=code_;
by date;
id code;
var Code_Amt_sum;
run;
Do you need a report or a dataset?
Proc tabulate would work for a report.
If you need a dataset, try this:
proc sql;
create table have_sum as
select
a.date,
b.code,
coalesce(sum(code_amt), 0) as code_sum
from
(select unique date from have) as a cross join
(select unique code from have) as b left join
have as c on a.date=c.date and b.code=c.code
group by a.date, b.code;
quit;
proc transpose data=have_sum out=want(drop=_name_) prefix=code_;
by date;
id code;
var code_sum;
run;
proc tabulate data=have;
class date code;
var Code_Amt;
table date,code*sum=''*Code_Amt='' /misstext='0';
run;
Or a mix of the 2 solutions above.
proc tabulate data=have out=have_sum;
class date code;
var Code_Amt;
table date,code*sum=''*Code_Amt='' ;
run;
proc transpose data=have_sum out=want(drop=_name_) prefix=code_;
by date;
id code;
var Code_Amt_sum;
run;
@ChrisNZ, good idea, but you get missing values and not zeros for missing code sums.
@PGStats True. If that's an actual requirement, you can always add the coalesce calls in a third step. It will be very fast on the summarised data. So many options...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.