topic Re: Monthly Report Transpose in SAS Programming

Monthly Report Transpose

BCNAV — Mon, 29 Jul 2019 19:07:10 GMT

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

Re: Monthly Report Transpose

Kurt_Bremser — Mon, 29 Jul 2019 19:14:18 GMT

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.

Re: Monthly Report Transpose

BCNAV — Mon, 29 Jul 2019 19:44:44 GMT

thx...just had to sort first!!

Re: Monthly Report Transpose

novinosrin — Mon, 29 Jul 2019 20:17:20 GMT


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;