topic Re: Looping through variables when importing data in SAS Programming

Looping through variables when importing data

sasfyj — Thu, 03 Jun 2021 03:22:20 GMT

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.

Re: Looping through variables when importing data

blueskyxyz — Thu, 03 Jun 2021 03:51:40 GMT

please upload a sample dataset for the test

Re: Looping through variables when importing data

ChrisNZ — Thu, 03 Jun 2021 04:18:23 GMT

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;

Re: Looping through variables when importing data

ChrisNZ — Thu, 03 Jun 2021 04:20:45 GMT

Another way:

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

Re: Looping through variables when importing data

Oligolas — Thu, 03 Jun 2021 08:18:42 GMT

@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;

Re: Looping through variables when importing data

FreelanceReinh — Thu, 03 Jun 2021 08:24:05 GMT

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;

Re: Looping through variables when importing data

ChrisNZ — Thu, 03 Jun 2021 10:22:25 GMT

> you need a starting anchor

You mean the tilde I add before catx() ?

Re: Looping through variables when importing data

sasfyj — Thu, 03 Jun 2021 23:01:20 GMT

Thanks. It works as well.

Re: Looping through variables when importing data

sasfyj — Thu, 03 Jun 2021 23:01:59 GMT

Thank you. It works properly.

Re: Looping through variables when importing data

sasfyj — Thu, 03 Jun 2021 23:02:54 GMT

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

Re: Looping through variables when importing data

Oligolas — Fri, 04 Jun 2021 08:27:55 GMT

@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.