DATA Step, Macro, Functions and more

Using arrays to parse text strings

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Using arrays to parse text strings

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.


Accepted Solutions
Solution
‎06-09-2017 12:12 AM
PROC Star
Posts: 1,760

Re: Using arrays to parse text strings

[ Edited ]

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

View solution in original post


All Replies
PROC Star
Posts: 1,760

Re: Using arrays to parse text strings

Posted in reply to _Rachael_

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.
Solution
‎06-09-2017 12:12 AM
PROC Star
Posts: 1,760

Re: Using arrays to parse text strings

[ Edited ]

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
New Contributor
Posts: 3

Re: Using arrays to parse text strings

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!

PROC Star
Posts: 1,760

Re: Using arrays to parse text strings

Posted in reply to _Rachael_

No worries. Smiley Happy

 

> 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; 
 
New Contributor
Posts: 3

Re: Using arrays to parse text strings

Thanks Chris, you're right, I can do that. :-)

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 133 views
  • 0 likes
  • 2 in conversation