SAS: Data Step. By Processing

Question

How can I aggregate the following sample data to give customer-level calculations? I'm using a data step with 'by processing', but I'm not sure whether or not I should break this up into two data steps or not.

I need to extract the first type, first price, a count of types, a count of unique prices, a count for soccer bets and a count for baseball bets for each player.

I can't seem to combine both the type and price in the same data step.

data have;
input username $  betdate : datetime. stake type $ price sport $;
dateOnly = datepart(betdate) ;
format betdate DATETIME.;
format dateOnly ddmmyy8.;
datalines; 
player1 12NOV2008:12:04:01 90 SGL 5 SOCCER
player1 04NOV2008:09:03:44 30 SGL 4  SOCCER
player2 07NOV2008:14:03:33 120 SGL 5 SOCCER
player1 05NOV2008:09:00:00 50 SGL 4 SOCCER
player1 05NOV2008:09:05:00 30 DBL 3 BASEBALL 
player1 05NOV2008:09:00:05 20 DBL 4 BASEBALL 
player2 09NOV2008:10:05:10 10 DBL 5 BASEBALL 
player2 15NOV2008:15:05:33 35 DBL 5 BASEBALL 
player1 15NOV2008:15:05:33 35 TBL 5 BASEBALL
player1 15NOV2008:15:05:33 35 SGL 4 BASEBALL
run;
proc print;run;

proc sort data=have; by username dateonly betdate type price; run;
data want;
set have;
retain typecount pricecount firsttype firstprice soccercount baseballcount;
by username dateonly betdate;   
if first.username then eventTime = 0;
if first.betdate then eventTime + 1;

if first.username then soccercount=0;
if first.username then baseballcount=0;
if index(upcase(sport),'SOCCER') and eventtime <=5 then soccercount+1;
else if eventtime <=5 then baseballcount+1;

if first.username and eventtime =1 then firsttype=type;
else if eventtime =1 then firsttype=type;

if first.username and eventtime =1 then firstprice=price;
else if eventtime =1 then firstprice=price;

if first.username then typecount=0;
if first.type then typecount+1;

if first.username then pricecount=0;
if first.price and eventtime <=5 then pricecount+1;

IF last.username THEN OUTPUT;
keep username soccercount baseballcount firsttype firstprice typecount pricecount;
run;
proc print;run;

Do you want a count of unique types or a count of how many times the type changes? i.e. should player1 be 3 or 4? — Longfish
– Longfish, Commented Mar 10, 2014 at 12:49

user3272403 · Accepted Answer · 2014-03-26 17:59:48Z

1

this should do want you've requested within one datastep:

proc sort data =have; by by username dateonly betdate;   run; 

data want(drop=  betdate dateonly  stake type  price sport TYPELIST PRICELIST) ;
set have;
LENGTH TYPELIST PRICELIST $200; *ARBITRARY LARGE LENGTH;

retain firsttype firstprice TYPELIST typecount PRICELIST pricecount soccercount baseballcount;
by username dateonly betdate;   

if first.username then do ; 
    firsttype=type; 
    firstprice=PRICE; 
     typecount=0; pricecount=0; soccercount=0; baseballcount=0; 
    TYPELIST=""; PRICELIST="";
END; 

if index(upcase(sport),'SOCCER') then soccercount+1;
if index(upcase(sport),'BASEBALL') then baseballcount+1;

IF find(TYPELIST,TYPE,'it')=0 THEN TYPELIST=CATX("|",TYPELIST,TYPE); 
IF findc(PRICELIST,PRICE,'it')=0 THEN PRICELIST=CATX("|",PRICELIST,PRICE); 

IF last.username THEN DO; 
 typecount=LENGTH(TYPELIST)-LENGTH(COMPRESS(TYPELIST,"|"))+1; 
 pricecount=LENGTH(PRICELIST)-LENGTH(COMPRESS(PRICELIST,"|"))+1; 

OUTPUT; 
END; 
run;
proc print data=want;run;

edited Mar 26, 2014 at 17:59

answered Mar 10, 2014 at 21:25

user3272403

1063 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2146441 Over a year ago

The solution you posted gives an incorrect count for pricecount. The result for player 2 shows 0.

user3272403 Over a year ago

my apologies! i left off the "+1" on the calculation of the difference in the list with and with out pipes. As a result each was off by one. I've adjust the code to reflect the correct syntax.

Collectives™ on Stack Overflow

SAS: Data Step. By Processing

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related