Using arrays to parse text strings

[_Rachael_]

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.

[ChrisNZ]

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

If you want the names to be more abstracted then you'll need to use the macro language.

[ChrisNZ · Accepted solution]

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

[_Rachael_]

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!

[ChrisNZ]

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; 
 

[_Rachael_]

Thanks Chris, you're right, I can do that. 🙂