DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor Moh
Contributor
Posts: 20
Accepted Solution

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

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;

Accepted Solutions
Solution
‎02-09-2016 06:04 AM
Super User
Posts: 11,343

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

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.

View solution in original post


All Replies
Super User
Posts: 11,343

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

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?

Contributor Moh
Contributor
Posts: 20

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

Hi @ballardw

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

Super User
Posts: 5,437

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

Either way, since "variable" (not a brilliant name, can cause confusion) is character you need to enclose the list values within quotes, 'x1' etc.
Data never sleeps
Trusted Advisor
Posts: 1,118

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

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

Trusted Advisor
Posts: 1,118

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

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

Contributor Moh
Contributor
Posts: 20

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

Posted in reply to FreelanceReinhard

Thanks @FreelanceReinhard 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"

Solution
‎02-09-2016 06:04 AM
Super User
Posts: 11,343

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

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.

Trusted Advisor
Posts: 1,118

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

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';
Super User
Posts: 5,437

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

@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.
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 319 views
  • 2 likes
  • 4 in conversation