DATA Step, Macro, Functions and more

create a summing proc transpose

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

create a summing proc transpose

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 


Accepted Solutions
Solution
‎05-27-2016 11:02 PM
PROC Star
Posts: 1,760

Re: create a summing proc transpose

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;

View solution in original post


All Replies
Super User
Posts: 19,855

Re: create a summing proc transpose

Do you need a report or a dataset?

 

Proc tabulate would work for a report. 

Respected Advisor
Posts: 4,930

Re: create a summing proc transpose

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;
PG
Trusted Advisor
Posts: 1,228

Re: create a summing proc transpose

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

Solution
‎05-27-2016 11:02 PM
PROC Star
Posts: 1,760

Re: create a summing proc transpose

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;

Respected Advisor
Posts: 4,930

Re: create a summing proc transpose

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

PG
PROC Star
Posts: 1,760

Re: create a summing proc transpose

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

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 545 views
  • 2 likes
  • 5 in conversation