topic Re: How to choose a list of variables from a specific column and save it in a macro in SAS Programming

How to choose a list of variables from a specific column and save it in a macro

Moh — Mon, 08 Feb 2016 20:56:44 GMT

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;

Re: How to choose a list of variables from a specific column and save it in a macro

ballardw — Mon, 08 Feb 2016 21:30:36 GMT

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?

Re: How to choose a list of variables from a specific column and save it in a macro

LinusH — Mon, 08 Feb 2016 21:36:27 GMT

Either way, since "variable" (not a brilliant name, can cause confusion) is character you need to enclose the list values within quotes, 'x1' etc.

Re: How to choose a list of variables from a specific column and save it in a macro

FreelanceReinh — Mon, 08 Feb 2016 21:47:55 GMT

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

Re: How to choose a list of variables from a specific column and save it in a macro

Moh — Mon, 08 Feb 2016 21:53:11 GMT

Hi @ballardw

In my case duplicated values is not possible, neither the order and case sensitive....Thanks

Re: How to choose a list of variables from a specific column and save it in a macro

Moh — Mon, 08 Feb 2016 21:55:46 GMT

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"

Re: How to choose a list of variables from a specific column and save it in a macro

ballardw — Mon, 08 Feb 2016 22:01:20 GMT

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.

Re: How to choose a list of variables from a specific column and save it in a macro

FreelanceReinh — Mon, 08 Feb 2016 22:02:26 GMT

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

Re: How to choose a list of variables from a specific column and save it in a macro

FreelanceReinh — Mon, 08 Feb 2016 22:06:27 GMT

@LinusH: Wouldn't quotes become a hindrance if &varlist was used, e.g., in a KEEP statement?

Re: How to choose a list of variables from a specific column and save it in a macro

LinusH — Tue, 09 Feb 2016 06:51:07 GMT

@freelancereinhard as the OP presents x1 X2 x3 are values in the variable variable (!). A keep statement shouldn't be relevant unless any transposing occurs. A bit hypothetical from the information we have at hand.