BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BCNAV
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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
Quartz | Level 8
thx...just had to sort first!!
novinosrin
Tourmaline | Level 20

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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 819 views
  • 1 like
  • 3 in conversation