Monthly Report Transpose

[BCNAV]

This is probably simple, but here goes.  Data is structured as monthly files...so each file has records from the days of a month.  It looks like:

Date      Market   Revenue

July 1    1        5

July 1    1        6

July 2    2        4

July 3    3        6

There can be many transactions per day over the month.  I'd like to make:

Date and Market Revenue for each market in the horizontal, for each market.  Example

Date     1      2      3

July 1   11     0      0

July 2   0      4      0

July 3   0      0      6

As you can see, there is one date for a day, then the horizontal is the sum of each market's revenue (markets are 1,2,3, etc) for that day.

thx

[Kurt_Bremser · Accepted solution]

First, do a proc summary by date and market for variable revenue. Then do a transpose by date, id market and var revenue,

proc summary data=have;
by date market;
var revenue;
output
  out=summary (keep=date market revenue)
  sum(revenue)=revenue
;
run;

proc transpose data=summary out=want prefix=market_;
by date;
id market;
var revenue;
run;

You may have to tinker around a little with proc summary to only get the desired observations.

[BCNAV]

thx...just had to sort first!!

[novinosrin]

data have;
input Date & $10.      Market   Revenue;
cards;
July 1    1        5
July 1    1        6
July 2    2        4
July 3    3        6
;


/*Get the max of the market for sizing up the array elements*/
proc sql;
select max(market) into :m trimmed
from have;
quit;

data want;
if 0 then set have;
array market_(&m)(&m*0);
retain _k;
if _n_=1 then _k=peekclong(addrlong(market_(1)),&m*8);
do until(last.market);
set have;
by date market;
if first.market then call pokelong(_k,addrlong(market_(1)),&m*8);
_sum=sum(_sum,revenue);
end;
market_(market)=_sum;
keep date market_:;
run;