Hi,
I have a dataset with lots of variables that capture the numbers of each type of product that a customer has at different points in time. The time components of all the variables are labelled in the same fashion but the text referring to the product differs.
E.g. red_t1, red_t2, red_t3,
blue_t1, blue_t2, blue_t3,
white_t1, white_t2, white_t3
I want to create a series of difference variables and use some sort of array to parse the product names to make the code more efficient. Ideally, I'm thinking something like this:
data new;
set old;
array prod[3] red blue white;
do i = 1 to 3;
prod[i]_diff2=prod[i]_t2-prod[i]_t1;
prod[i]_diff3=prod[i]_t3-prod[i]_t2;
end;
run;
However, I haven't been able to figure out a way to use arrays to parse text rather than refer to an actual variable.
Any help would be greatly appreciated! Thank-you.
Macro language example:
data HAVE;
retain RED_T1 1 RED_T2 2 RED_T3 4
BLUE_T1 1 BLUE_T2 3 BLUE_T3 6
WHITE_T1 1 WHITE_T2 4 WHITE_T3 8
GREEN_T1 1 GREEN_T2 4 GREEN_T3 9 ;
run;
%macro diffs;
%local names name namenb time;
%let names=RED BLUE WHITE GREEN;
data WANT;
set HAVE;
%do namenb=1 %to %sysfunc(countw(&names,%str( )));
%let name=%scan(&names,&namenb,%str( ));
%do time=1 %to 2;
&name._D%eval(&time+1)=&name._T%eval(&time+1)-&name._T&time;
%end;
%end;
run;
%mend;
%diffs;
proc print noobs;
var RED_D2-RED_D3 BLUE_D2-BLUE_D3 WHITE_D2-WHITE_D3 GREEN_D2-GREEN_D3;
run;
RED_D2 | RED_D3 | BLUE_D2 | BLUE_D3 | WHITE_D2 | WHITE_D3 | GREEN_D2 | GREEN_D3 |
---|---|---|---|---|---|---|---|
1 | 2 | 2 | 3 | 3 | 4 | 3 | 5 |
LIke this?
data HAVE;
retain RED_T1 4 RED_T2 2 RED_T3 1
BLUE_T1 6 BLUE_T2 3 BLUE_T3 1
WHITE_T1 8 WHITE_T2 4 WHITE_T3 1
GREEN_T1 9 GREEN_T2 4 GREEN_T3 1;
run;
data WANT;
set HAVE;
array COUNTS[*] RED_T1-RED_T3 BLUE_T1-BLUE_T3 WHITE_T1-WHITE_T3 GREEN_T1-GREEN_T3;
array DIFFS[*] RED_D2-RED_D3 BLUE_D2-BLUE_D3 WHITE_D2-WHITE_D3 GREEN_D2-GREEN_D3;
do PRODNB=0 to 3;
do TIME=1 to 2;
DIFFS[PRODNB*2+TIME]=COUNTS[PRODNB*3+TIME]-COUNTS[PRODNB*3+TIME+1];
end;
end;
run;
proc print noobs;
var RED_D2-RED_D3 BLUE_D2-BLUE_D3 WHITE_D2-WHITE_D3 GREEN_D2-GREEN_D3;
run;
RED_D2 | RED_D3 | BLUE_D2 | BLUE_D3 | WHITE_D2 | WHITE_D3 | GREEN_D2 | GREEN_D3 |
---|---|---|---|---|---|---|---|
2 | 1 | 3 | 2 | 4 | 3 | 5 | 3 |
Macro language example:
data HAVE;
retain RED_T1 1 RED_T2 2 RED_T3 4
BLUE_T1 1 BLUE_T2 3 BLUE_T3 6
WHITE_T1 1 WHITE_T2 4 WHITE_T3 8
GREEN_T1 1 GREEN_T2 4 GREEN_T3 9 ;
run;
%macro diffs;
%local names name namenb time;
%let names=RED BLUE WHITE GREEN;
data WANT;
set HAVE;
%do namenb=1 %to %sysfunc(countw(&names,%str( )));
%let name=%scan(&names,&namenb,%str( ));
%do time=1 %to 2;
&name._D%eval(&time+1)=&name._T%eval(&time+1)-&name._T&time;
%end;
%end;
run;
%mend;
%diffs;
proc print noobs;
var RED_D2-RED_D3 BLUE_D2-BLUE_D3 WHITE_D2-WHITE_D3 GREEN_D2-GREEN_D3;
run;
RED_D2 | RED_D3 | BLUE_D2 | BLUE_D3 | WHITE_D2 | WHITE_D3 | GREEN_D2 | GREEN_D3 |
---|---|---|---|---|---|---|---|
1 | 2 | 2 | 3 | 3 | 4 | 3 | 5 |
Thanks very much for your help Chris.
The time periods are not expressed quite so elegantly as in my example, so I definitely want the added flexibility of the macro solution.
It is a shame that this requires it's own data step though. It would be great if SAS had a way of programming it as simply as I'd hoped. In the meantime, thanks, I'll be sharing this solution with my colleagues and I'm sure it will get a great work out!
No worries. 🙂
> this requires its own data step
I doesn't:
data WANT;
set HAVE;
<more code>
%macro diffs;
%local names name namenb time;
%let names=RED BLUE WHITE GREEN;
%do namenb=1 %to %sysfunc(countw(&names,%str( )));
%let name=%scan(&names,&namenb,%str( ));
%do time=1 %to 2;
&name._D%eval(&time+1)=&name._T%eval(&time+1)-&name._T&time;
%end;
%end;
%mend;
%diffs;
<more code>
run;
Thanks Chris, you're right, I can do that. 🙂
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.