topic Re: Macro variable string into several parts divided by commas and quotes. in SAS Programming

Macro variable string into several parts divided by commas and quotes.

allen_AL — Fri, 13 Jul 2018 15:49:59 GMT

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=

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.

Re: Macro variable string into several parts divided by commas and quotes.

Reeza — Fri, 13 Jul 2018 15:19:27 GMT

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.

Re: Macro variable string into several parts divided by commas and quotes.

allen_AL — Fri, 13 Jul 2018 15:30:11 GMT

/*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.

Re: Macro variable string into several parts divided by commas and quotes.

Astounding — Fri, 13 Jul 2018 15:31:34 GMT

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;

Re: Macro variable string into several parts divided by commas and quotes.

allen_AL — Fri, 13 Jul 2018 15:32:18 GMT

The variable list came from word.

Re: Macro variable string into several parts divided by commas and quotes.

Astounding — Fri, 13 Jul 2018 15:35:53 GMT

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;

Re: Macro variable string into several parts divided by commas and quotes.

Reeza — Fri, 13 Jul 2018 15:36:21 GMT

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.

Re: Macro variable string into several parts divided by commas and quotes.

allen_AL — Fri, 13 Jul 2018 15:37:45 GMT

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.

Re: Macro variable string into several parts divided by commas and quotes.

ballardw — Fri, 13 Jul 2018 15:37:53 GMT

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?

Re: Macro variable string into several parts divided by commas and quotes.

allen_AL — Fri, 13 Jul 2018 15:40:30 GMT

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.

Re: Macro variable string into several parts divided by commas and quotes.

allen_AL — Fri, 13 Jul 2018 15:42:42 GMT

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.

Re: Macro variable string into several parts divided by commas and quotes.

Reeza — Fri, 13 Jul 2018 15:43:34 GMT

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

Re: Macro variable string into several parts divided by commas and quotes.

allen_AL — Fri, 13 Jul 2018 15:49:13 GMT

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=

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.

Re: Macro variable string into several parts divided by commas and quotes.

ballardw — Fri, 13 Jul 2018 15:50:48 GMT

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

Re: Macro variable string into several parts divided by commas and quotes.

Reeza — Fri, 13 Jul 2018 15:52:45 GMT

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-around-words-within/td-p/363490

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

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.

Re: Macro variable string into several parts divided by commas and quotes.

allen_AL — Fri, 13 Jul 2018 18:53:40 GMT

Thank you very much for your quick response.

Re: Macro variable string into several parts divided by commas and quotes.

Tom — Fri, 13 Jul 2018 21:36:01 GMT

It sounds like want to convert a space delimited list of tokens to a quoted list.

First make sure you have just one space between "words". Then wrap in quotes and replace the internal spaces with quoted spaces.

%let list=a b 
c d e
f

g

;
%let list=%sysfunc(compbl(&list));
%let qlist="%sysfunc(tranwrd(&list,%str( )," "))";

Not sure why you want to add commas also, since SAS does not require them. Put you could use "," instead of " " and then you would have commas between the quoted strings.

If you are really copying from WORD make sure that what you copy doesn't include strange non-printing characters instead of normal spaces.