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
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.
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.
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.