topic Re: create a summing proc transpose in SAS Programming

create a summing proc transpose

Beto16 — Fri, 06 May 2016 01:40:29 GMT

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

Re: create a summing proc transpose

Reeza — Fri, 06 May 2016 02:17:38 GMT

Do you need a report or a dataset?

Proc tabulate would work for a report.

Re: create a summing proc transpose

PGStats — Fri, 06 May 2016 03:26:11 GMT

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;

Re: create a summing proc transpose

stat_sas — Fri, 06 May 2016 04:00:26 GMT

proc tabulate data=have;
class date code;
var Code_Amt;
table date,code*sum=''*Code_Amt='' /misstext='0';
run;

Re: create a summing proc transpose

ChrisNZ — Fri, 06 May 2016 05:37:59 GMT

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;

Re: create a summing proc transpose

PGStats — Fri, 06 May 2016 16:05:08 GMT

@ChrisNZ, good idea, but you get missing values and not zeros for missing code sums.

Re: create a summing proc transpose

ChrisNZ — Sun, 08 May 2016 22:26:38 GMT

@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...