Hi Folks, I have a dataset and need to pick up the observations ( of the first column as variable_list (say, &varlist).
Therefore, I want to end up with;
%put &varlist ;
varlist=x1 x2 x3
would you please help me in this regard? Thanks
data zyz; length variable $ 4; input variable R B C; cards; x1 1 2 3 x2 5 6 7 x3 8 9 10; run;
To exclude use WHERE
proc sql noprint; select variable into :varlist separated by ' ' from zyz where variable ne "constant" ; quit;
The where clause could use functions to exclude values that start with a letter: where substr(variable,1,1) ne 'b'
or a list of characters where substr(variable,1,1) not in ( 'b' 'c' 'q')
or a lot of other ways to compare things.
What do you want if any of the values of "variable" are duplicated?
Does the order have to match the order in the data set?
Are there any case sensitive issues to be concerned about such as values of x1 and X1 should be treated the same?
Hi @ballardw
In my case duplicated values is not possible, neither the order and case sensitive....Thanks
@LinusH: Wouldn't quotes become a hindrance if &varlist was used, e.g., in a KEEP statement?
Hi @Moh,
As a basic approach you could try this:
proc sql noprint;
select variable into :varlist separated by ' '
from zyz;
quit;
%put &=varlist;
If, for example, duplicates are to be eliminated, disregarding case, you could adapt the SELECT statement accordingly:
select distinct(lowcase(variable)) into ...
(This would result in a sorted list due to the DISTINCT keyword.)
Thanks @FreelanceReinh for your answer.
How if one of the variables is not starting with x , for example, its name is "constant".
How can I get rid of "constant"
To exclude use WHERE
proc sql noprint; select variable into :varlist separated by ' ' from zyz where variable ne "constant" ; quit;
The where clause could use functions to exclude values that start with a letter: where substr(variable,1,1) ne 'b'
or a list of characters where substr(variable,1,1) not in ( 'b' 'c' 'q')
or a lot of other ways to compare things.
To exclude "constant" from the selection you can insert a WHERE condition into the PROC SQL step:
proc sql noprint;
select variable into :varlist separated by ' '
from zyz
where lowcase(variable) ne 'constant';
quit;
To restrict the selection to variables whose names start with 'x' (case insensitive), you could use the following WHERE condition:
where lowcase(variable) eqt 'x';
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.