topic Re: Multiple transaction lines to base table in SAS Programming

Multiple transaction lines to base table

AntrOsl — Sun, 18 Feb 2018 13:13:37 GMT

I am new to sas and are trying to handle some customer data, and I'm not really sure how to do this.

What I have:

data transactions;                      
input ID $  Week Segment $ Average Freq;       
datalines;            
1 1 Sports 500 2
1 1 PC 400 3
1 2 Sports 350 3
1 2 PC 550 3
2 1 Sports 650 2
2 1 PC 700 3
2 2 Sports 720 3
2 2 PC 250 3
;                                    
run;

What I want:

data transactions2;
input ID Week1_Sports_Average Week1_PC_Average Week1_Sports_Freq 
Week1_PC_Freq
Week2_Sports_Average Week2_PC_Average Week2_Sports_Freq Week2_PC_Freq;
datalines;
1 500 400 2 3 350 550 3 3 
2 650 700 2 3 720 250 3 3
;
run;

The only thing I got so far is:

 Data transactions3;
 SET transactions;
 if week=1 and Segment="Sports" then DO; 
 Week1_Sports_Freq=Freq; 
 Week1_Sports_Average=Average;
 END;
 else DO;
 Week1_Sports_Freq=0; 
 Week1_Sports_Average=0;
 END;
 run;

This will be way too much work as I have a lot of weeks and more variables than just freq/avg.

Really hoping for some tips are, as I'm stucked.

Re: Multiple transaction lines to base table

Ksharp — Sun, 18 Feb 2018 14:11:39 GMT

data transactions;                      
input ID $  Week Segment $ Average Freq;       
datalines;            
1 1 Sports 500 2
1 1 PC 400 3
1 2 Sports 350 3
1 2 PC 550 3
2 1 Sports 650 2
2 1 PC 700 3
2 2 Sports 720 3
2 2 PC 250 3
;                                    
run; 

proc sql noprint;
select distinct catt('transactions(where=(week=',week,' and segment="',segment,'") 
 rename=(average=week',week,'_',segment,'_average 
 freq=week',week,'_',segment,'_freq))') into : merge separated by ' '
 from transactions;
quit;
data want;
 merge &merge;
 by id;
 drop Week Segment;
run;
proc print noobs;run;

Re: Multiple transaction lines to base table

kiranv_ — Sun, 18 Feb 2018 15:37:25 GMT

cool answer @Ksharp but may have to do lot of merges, if the dataset has 1000 id's or more.

Re: Multiple transaction lines to base table

Astounding — Sun, 18 Feb 2018 17:08:03 GMT

Your starting data set reflects the way that data would normally be stored, and is most flexible.

Your intended transformation of the data is much more limited in how it can be processed. What is your ultimate goal, that you want to do this at all? There may be a way to get there using your original data. Being new to SAS, your time is likely to be better spent learning how to program with the original data, instead of transforming it into a less flexible format.

Re: Multiple transaction lines to base table

novinosrin — Sun, 18 Feb 2018 22:22:15 GMT

data transactions;                      
input ID $  Week Segment $ Average Freq;       
datalines;            
1 1 Sports 500 2
1 1 PC 400 3
1 2 Sports 350 3
1 2 PC 550 3
2 1 Sports 650 2
2 1 PC 700 3
2 2 Sports 720 3
2 2 PC 250 3
;                                    
run; 
proc transpose data=transactions out=temp prefix=week;
by id ;
var average freq;
id  week segment;
run;

data avg(where=(_name_='Average') ) freq( where=(_name_='Freq') );
set temp;
run;

data final_want;
merge avg(drop=_name_) freq(drop=_name_ rename=(week1sports=week1sports_freq week1pc= week1pc_freq week2pc=week2pc_freq week2sports=week2sports_freq));
by id;
run;

Notes:

1. Renaming is the only boring and verbose thing in this exercise

2. Renaming is required in the last step in the statement --- merge avg(rename here with average suffix) freq(rename here with freq suffix)

3. For dynamic renaming, Do refer to http://support.sas.com/kb/48/674.html for a easy copy paste that helps lazy folks like me

Re: Multiple transaction lines to base table

novinosrin — Sun, 18 Feb 2018 23:27:03 GMT

This seems even easier:

data transactions;                      
input ID $  Week Segment $ Average Freq;       
datalines;            
1 1 Sports 500 2
1 1 PC 400 3
1 2 Sports 350 3
1 2 PC 550 3
2 1 Sports 650 2
2 1 PC 700 3
2 2 Sports 720 3
2 2 PC 250 3
;                                    
run; 

data temp1;
set transactions;
temp=average;
_t1=catx('_','week'||left(week),segment,vname(average));
output;
temp=freq;
_t1=catx('_','week'||left(week),segment,vname(freq));
output;
run;

proc transpose data=temp1 out=want(drop=_name_) ;
by id ;
var temp;
id  _t1;
run;

Re: Multiple transaction lines to base table

AntrOsl — Mon, 19 Feb 2018 09:59:19 GMT

Thank you all so much for your help. In the end I managed to do it this way:

proc transpose data=transactions out=tall ;
  by id week segment notsorted;
  var average freq ;
run;

data tall ;
  set tall ;
  length new_name $32 ;
  new_name = catx('_',cats('WEEK',week),segment,_name_);
run;
proc transpose data=tall out=want ;
  by id;
  id new_name;
  var col1 ;
run;

Re: Multiple transaction lines to base table

Ksharp — Thu, 22 Feb 2018 03:53:30 GMT

Not really. The factor affect the number of merging table is WEEK and the third variable.

No Matter how many subject id you have, if there were only four different combination of them ,you only need merge four tables.