create a summing proc transpose

[Beto16]

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 

[Reeza]

Do you need a report or a dataset?

Proc tabulate would work for a report. 

[PGStats]

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;

[stat_sas]

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

[ChrisNZ · Accepted solution]

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;

[PGStats]

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

[ChrisNZ]

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