Looping through variables when importing data

[sasfyj]

Hi there,

I have a SAS dataset called sasdata1 which has 40 variables with 20 of them named "attrib1" to "attrib20". There is also a variable called numattrib that stores the number of non-empty attributes starting from attrib1 (Obviously the value of numattrib could be different for different records). I want to create a sub-dataset called sasdata2 with records meeting the condition that any of attrib1-attrib20 starts with either "123" or "13a" or "2a1". Here is my SAS code:

DATA sasdata2;
SET sasdata1;
if

Substr(attrib1,1,3) in ('123','13a','2a1') or
Substr(attrib2,1,3) in ('123','13a','2a1') or

....

Substr(attrib20,1,3) in ('123','13a','2a1') or
;
RUN;

My first question is whether it is possible to consolidate these 20 lines of code after the key word if. Second question is whether we can take advantage of the variable numattrib to design a more efficient import process.

Thank you very much in advance. 

[blueskyxyz]

please upload a sample dataset for the test

[ChrisNZ]

Would this work for you?

if index('|'||catx('|',of ATTRIB1-ATTRIB20), "|123") 
 | index('|'||catx('|',of ATTRIB1-ATTRIB20), "|13a") 
 | index('|'||catx('|',of ATTRIB1-ATTRIB20), "|2a1") then output;

[ChrisNZ]

Another way:

if prxmatch('/(~123|~13a|~2a1)/', '~'||catx('~',of ATTRIB1-ATTRIB20) ) then output;

[Oligolas]

@Chris you need a starting anchor in the regex so IMHO catx is not suitable here

maybe something like this:

ARRAY attribs ATTRIB1-ATTRIB20;
DO i=1 to dim(ATTRIBS);
if not prxmatch('/^(123|13a|2a1)/',ATTRIBS(i)) then leave;
if i eq 20 then output;
END;
drop i;

[ChrisNZ]

>  you need a starting anchor

You mean the tilde I add before catx() ?

[Oligolas]

@ChrisNZ your solution is correct, my bad.

I meant that all the variables had to meet the prefix defined, but one is enough according to the requirements.

This is definitely haute couture of programming as I like it.

[sasfyj]

Thanks for your help. I think we should remove 'Not' and change eq to le

[sasfyj]

Thank you. It works properly.

[sasfyj]

Thanks. It works as well. 

[FreelanceReinh · Accepted solution]

Hi @sasfyj and welcome to the SAS Support Communities!

To utilize numattrib use an iterative DO loop "... TO numattrib."

Example:

data sasdata2(drop=i);
set sasdata1;
array a[*] attrib1-attrib20;
do i=1 to numattrib until(a[i] in: ('123','13a','2a1'));
end;
if i<=numattrib;
run;