BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
allen_AL
Calcite | Level 5

Hi,

 

Thank you for reading. I have a long list with different variables in a macro variable. I want to use that list in a statement like " where variable in ("var1","var2",...) so I can filter rows in a third data set. The vars come from word or somewhere I can't import into SAS. I have to copy and paste this list every time, with different variables.

 

A recap of what I am trying to do:

 

I have dataset A which contains several records with thousands of variable names in a the column "variables". Kind of like:

 

Variable  Value1

A 1
A 1
A 2

B 1

B 4

C 45

C 56

C 4

F 4

D 2345

D 22

 

I need to filter the rows that contain the Variable "A","C", and "F".

 

This is a very simple task, the problem is that the people who will use the macro can only paste the variables they want on 

%let vars=

A

C
F

;

 

Having said that, my code is as follows:

 

 


%let vars=
Sxxxf_w
Sxxxa_w
Sxxxs_w
Slxxs_w
rSddd_w
Sfssss_w
Sssss_w
Sfffb_w
Sjjjs_w
Sllls_w
Ftxxs_w
Dfxxs_w
Ltxxs_w
Ytxxs_w
;

 

/*So I can do something like

proc sql;

select * from DATA

where variable in ("Sxxxf_w", "Sxxxa_w","Sxxxs_w",...)

;

quit;

 

 

*/

 

/*My try is to create a small dataset of one column which contains all the words in my macro variable.*/ 

data filter_list;
input @;
_infile_=resolve(_infile_);
input vari $  @@ ;
datalines;
&vars
;
run;

 

The problem with this code is that SAS only reads the first 10 lines and if I increase the size of the names, sas reads even less lines.

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

By default DATALINES expects 80 columns of input. So your macro variable is only read for the first 80 characters. Since your example data values are each 7 characters (+1 space to separate)

Please see:

data work.filter_list;
   input @;
   length pdq $ 200;
   pdq=resolve(_infile_);
   do i=1 to countw(pdq);
      vari = scan(pdq,i);
      output;
   end;
   drop pdq i;
datalines;
&vars
;
run;

as an alternate approach, though I still can't see what you are doing. If your &var total length is likely to be much larger then increase the length of PDQ. If any of the individual items in vars exceed 8 characters you would need to assign a length to vari in the data step long enough to handle it.

 

 

Your SQL might have an issue with case since you were using the IN operator the values have to match case unless you use something such as UPCASE or LOWCASE on the variable and force the list to have consistent case. Or was the question how to create a quoted list from &vars?

View solution in original post

16 REPLIES 16
Reeza
Super User

What are you trying to do here? It's not clear what you're attempting and what issues you're having.

 

If you're trying to create a list of macro variables, what is your starting source? In general, it's easiest to read the data into SAS as a data set first and then convert to macro variables. See the SQL example below on how to create a macro variable list. 

 

proc sql noprint;
select quote(name) into :name_list separated by ", "
from sashelp.class;
quit;

%put &name_list;

 


@allen_AL wrote:

Hi,

 

Thank you for reading. I have a long list with different variables in a macro variable so I can use that list to filter rows.
%let vars=
Sxxxf_w
Sxxxa_w
Sxxxs_w
Slxxs_w
rSddd_w
Sfssss_w
Sssss_w
Sfffb_w
Sjjjs_w
Sllls_w
Ftxxs_w
Dfxxs_w
Ltxxs_w
Ytxxs_w
;

 

/*So I can do something like

proc sql;

select * from DATA

where variable in ("Sxxxf_w", "Sxxxa_w","Sxxxs_w",...)

;

quit;

 

 

*/

 

/*My try:*/ 

data filter_list;
@input @;
_infile_=resolve(_infile_);
@input vari $  @@ ;
datalines;
&vars
;
run;

 

The problem with this code is that SAS only reads the first 10 lines and if I increase the size of the names, sas reads even less lines.

 

Thank you.


 

allen_AL
Calcite | Level 5

/*So I can do something like

proc sql;

select * from DATA

where variable in ("Sxxxf_w", "Sxxxa_w","Sxxxs_w",...)

;

quit;

 

 

*/

I have a macro variable that is very long, composed of several strings. Based on that list, I need to create something like "Sxxxf_w", "Sxxxa_w","Sxxxs_w",... so I can filter a dataset using  where variable in ("Sxxxf_w", "Sxxxa_w","Sxxxs_w",...)

 

Thank you.

Astounding
PROC Star

A good place to start:  where did &VARS come from?  If it came from something like this:

 

proc sql;

select name into : vars separated by ' '

from source_table;

quit;

 

If that's the case you would better off eliminating macro language entirely.  Your final SQL could look more like this:

 

proc sql;

select * from DATA

where variable in (select name from source_table);

quit;

allen_AL
Calcite | Level 5

The variable list came from word. 

Astounding
PROC Star

OK, that morphs into the approach I suggested, as long as you have Word save the document as a text file.  Then:

 

data source_table;

infile 'path to text file with list of names';

length name $ 32;

input name;

run;

 

Then proceed with:

 

proc sql;

select * from DATA where

variable  in (select name from source_table);

quit;

allen_AL
Calcite | Level 5

Sorry for not being clear. Importing the list of variables is not a feasible or desirable thing to do. I copy and paste the list of variables. I need this macro so all the people can just open SAS, paste the list of variables they need, and click RUN.

ballardw
Super User

@allen_AL wrote:

Sorry for not being clear. Importing the list of variables is not a feasible or desirable thing to do. I copy and paste the list of variables. I need this macro so all the people can just open SAS, paste the list of variables they need, and click RUN.


So instead of pasting into a macro variable which adds a variety of potential issues paste into a data step:

data filter_list;
   length vari $ 32;
   input vari  ;
datalines;
Sxxxf_w
Sxxxa_w
Sxxxs_w
Slxxs_w
rSddd_w
Sfssss_w
Sssss_w
Sfffb_w
Sjjjs_w
Sllls_w
Ftxxs_w
Dfxxs_w
Ltxxs_w
Ytxxs_w
;
run;

Since the first post implied you were likely to be attempting to match variable names of some sort then I have assigned a length of 32 as that is the current maximum for SAS variable names.

 

Still have a concern with comparison and case though.

Reeza
Super User

Paste it into the editor

Read it into a data set 

Use SQL to create the list.

 

data my_list;
input words $20.;
cards;
Random1
Random2
Random3
;;;;
run;

proc sql noprint;
select quote(words) into :my_list separated by ", " from my_list;
quit;

%put &my_list;

Or use the data set as indicated by @Astounding

 


@allen_AL wrote:

The variable list came from word. 


 

allen_AL
Calcite | Level 5

Thanks for the reply. What I need to read the whole list of variables and paste them as a macro variable.

 

I basically need this:

 

 

%let vars= random1
random2
random3
...
random15
;

data
my_list; input words $20.; cards;&vars.
;
;;; run;

 The problem with my original code is that SAS is only readin the first 10 lines, somehow it just stops reading after that.

ballardw
Super User

By default DATALINES expects 80 columns of input. So your macro variable is only read for the first 80 characters. Since your example data values are each 7 characters (+1 space to separate)

Please see:

data work.filter_list;
   input @;
   length pdq $ 200;
   pdq=resolve(_infile_);
   do i=1 to countw(pdq);
      vari = scan(pdq,i);
      output;
   end;
   drop pdq i;
datalines;
&vars
;
run;

as an alternate approach, though I still can't see what you are doing. If your &var total length is likely to be much larger then increase the length of PDQ. If any of the individual items in vars exceed 8 characters you would need to assign a length to vari in the data step long enough to handle it.

 

 

Your SQL might have an issue with case since you were using the IN operator the values have to match case unless you use something such as UPCASE or LOWCASE on the variable and force the list to have consistent case. Or was the question how to create a quoted list from &vars?

allen_AL
Calcite | Level 5

Sometimes we will have 40 variables of different lengths, sometimes we will have 10 variables, sometimes 20, sometimes 15, and they vary in lengths.

 

Based on your response, this task is basically impossible given the restrictions for 80?

 

Thank you.

Reeza
Super User

@allen_AL wrote:

Sometimes we will have 40 variables of different lengths, sometimes we will have 10 variables, sometimes 20, sometimes 15, and they vary in lengths.

 

Based on your response, this task is basically impossible given the restrictions for 80?

 

Thank you.


No, you just have to explicitly set your length and not use the default.

allen_AL
Calcite | Level 5

A recap of what I am trying to do:

 

I have dataset A which contains several records with thousands of variable names in a the column "variables". Kind of like:

 

Variable  Value1

A 1
A 1
A 2

B 1

B 4

C 45

C 56

C 4

F 4

D 2345

D 22

 

I need to filter the rows that contain the Variable "A","C", and "F".

 

This is a very simple task, the problem is that the people who will use the macro can only paste the variables they want on 

%let vars=

A

C
F

;

 

That is the only part they can/want to modify. I need my macro to be able to read all different variables names without me having to adjust each time the length. So, if I adjust it once, will they be able to paste any kind of variables (typically the maximum will be 40 but there will be variables with a lot of characters like "super_long_variable_name_version_3".

 

Thank you.

Reeza
Super User

 

Several solutions since this really isn't a new question:

 

https://communities.sas.com/t5/Base-SAS-Programming/Transform-macro-variable-to-wrap-quot-quot-aroun...

 

https://communities.sas.com/t5/Base-SAS-Programming/add-single-quotes-and-commas-to-a-list-of-words/...

 

https://communities.sas.com/t5/Base-SAS-Programming/Quote-and-Unquote-a-list/td-p/205512

 


@allen_AL wrote:

A recap of what I am trying to do:

 

I have dataset A which contains several records with thousands of variable names in a the column "variables". Kind of like:

 

Variable  Value1

A 1
A 1
A 2

B 1

B 4

C 45

C 56

C 4

F 4

D 2345

D 22

 

I need to filter the rows that contain the Variable "A","C", and "F".

 

This is a very simple task, the problem is that the people who will use the macro can only paste the variables they want on 

%let vars=

A

C
F

;

 

That is the only part they can/want to modify. I need my macro to be able to read all different variables names without me having to adjust each time the length. So, if I adjust it once, will they be able to paste any kind of variables (typically the maximum will be 40 but there will be variables with a lot of characters like "super_long_variable_name_version_3".

 

Thank you.


 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 2801 views
  • 4 likes
  • 5 in conversation