topic Re: Quick way to list out sequential variables with same prefix in SAS Programming

Quick way to list out sequential variables with same prefix

soomx — Wed, 20 Mar 2019 06:18:32 GMT

I have two situations in which I need to list all of my variables (which are sequential and have the same prefix):

#1)

if age<60 or ^(first_exvar = other_var_1 or first_exvar = other_var_2 or first_exvar = other_var_3....first_exvar = other_var_200) then flag=1

Essentially, I need to make sure the value of variable first_exvar does not equal the value of variables other_var_1 through to other_var_200.

What is the best way to do this?

#2)

This is slight different than above:

proc sql;
create table ex_table as
select distinct idnum, date, age, max(visitnum) as maxvisitnum, other_var_1, other_var_2, other_var_3....other_var_200

from data

;

quit;

How to select all variables from other_var_1 to other_var_200?

Thanks!

Re: Quick way to list out sequential variables with same prefix

PeterClemmensen — Wed, 20 Mar 2019 07:20:43 GMT

1) Create an array of the 200 variables and use the WHICHC or WHICHN function to find the first occurrence of first_exvar being equal to one of the variables. If the function equals zero, then none of them does.

2) PROC SQL does not support variable listing like this. However, check out KSharps approach in the thread below. I think that is the best way for you 🙂

https://communities.sas.com/t5/SAS-Procedures/Proc-sql-select-group-variable-with-same-prefix/td-p/390784

Re: Quick way to list out sequential variables with same prefix

Kurt_Bremser — Wed, 20 Mar 2019 07:25:49 GMT

You can create a dynamic piece of code and store it in a macro variable for use in the data step:

data have;
input age first_exvar other_var_1 other_var_2;
cards;
60 1 2 1
;
run;

proc sql noprint;
select "first_exvar = " !! name into :compare_str separated by " or "
from dictionary.columns
where libname = 'WORK' and memname = 'HAVE' and upcase(name) like '%OTHER_VAR%';
quit;

%put &compare_str.;

Log excerpt:

40         %put &compare_str.;
first_exvar = other_var_1 or first_exvar = other_var_2