topic Re: How to handle spaces in a macro variable list in SAS Programming

How to handle spaces in a macro variable list

lstockman — Thu, 03 Jan 2019 22:50:29 GMT

Hello,

I have a list of counties that have spaces in their names. Rather than change all the spaces to underscores or remove them, is there a way I can format the list so the macro will run with the original value?

Here is the code I am trying to pass the macro 'list' through to create a file for each county in my list.

%macro loopit(lhjlist);
   %let n = %sysfunc(countw(&lhjlist));
   %do I=1 %to &n;
      %let val = %scan(&lhjlist,&I);

      data want_&val;
         set have;
         where county= "&val";
      run;

	%end;
%mend;

%let list= 
LOS ANGELES
CONTRA COSTA
SAN DIEGO
;

%loopit(&list);

Re: How to handle spaces in a macro variable list

PaigeMiller — Thu, 03 Jan 2019 22:56:31 GMT

Put some character between the county names, such as

%let list=LOS ANGELES~CONTRA COSTA~SAN DIEGO;

and then

%let n = %sysfunc(countw(&lhjlist,~));
%do I=1 %to &n;
      %let val = %scan(&lhjlist,&I,~);

Re: How to handle spaces in a macro variable list

novinosrin — Thu, 03 Jan 2019 23:06:52 GMT

@PaigeMiller 's recommendation is neat.

May I know your bigger objective or is it just splitting datasets as your code suggests?

Re: How to handle spaces in a macro variable list

lstockman — Thu, 03 Jan 2019 23:29:47 GMT

Thanks, that works for some things and is very helpful to know. In my code below, the where statement is working but the output dataset is only the first word of the county i.e. work.want_los and work.want_san. Makes sense as I don't think an output dataset can have a space (?) Ultimately, I want to output a .csv or .xls file for each county in my list. Can I do that without first making individual datasets for each?

Re: How to handle spaces in a macro variable list

andreas_lds — Fri, 04 Jan 2019 06:51:07 GMT

@lstockman wrote:

Thanks, that works for some things and is very helpful to know. In my code below, the where statement is working but the output dataset is only the first word of the county i.e. work.want_los and work.want_san. Makes sense as I don't think an output dataset can have a space (?) Ultimately, I want to output a .csv or .xls file for each county in my list. Can I do that without first making individual datasets for each?

You don't have to split data to create file for each county.

Look at http://support.sas.com/kb/24/599.html for an example.

Re: How to handle spaces in a macro variable list

Kurt_Bremser — Fri, 04 Jan 2019 09:06:35 GMT

First, sort by county.

Then do

data _null_;
set have;
by county;
length fname $200;
if first.county then fname = "&path./" !! strip(county) !! '.csv';
file dummy dlm=',' dsd filevar=fname;
put .....;
run;

For easier handling of the csv files, consider replacing the blanks in county with underlines, and convert everything to lowercase.

Re: How to handle spaces in a macro variable list

PaigeMiller — Fri, 04 Jan 2019 13:00:25 GMT

@lstockman wrote:

Thanks, that works for some things and is very helpful to know. In my code below, the where statement is working but the output dataset is only the first word of the county i.e. work.want_los and work.want_san. Makes sense as I don't think an output dataset can have a space (?) Ultimately, I want to output a .csv or .xls file for each county in my list. Can I do that without first making individual datasets for each?

Yes, you can do this without creating a SAS data set, and your .csv or .xls files CAN have space(s) in the file name. I believe Kurt Bremser has provided a solution.

You could also replace the space with an underscore in the data set names. This is probably unnecessary extra work given Kurt Bremser's solution, but for completeness, this gives you legal SAS data set names. Naturally, it becomes a pain to have data set names with underscores and corresponding data values that have spaces.

data want_%sysfunc(translate(&val,_,%str( )));

Re: How to handle spaces in a macro variable list

lstockman — Fri, 04 Jan 2019 17:51:41 GMT

Thank you @Kurt_Bremser ! This works and is so much faster! Do you know if there is a statement I can add to retain the variable names as the first row in the resulting csv files?

Re: How to handle spaces in a macro variable list

lstockman — Fri, 04 Jan 2019 17:52:31 GMT

Thank you @PaigeMiller!

Re: How to handle spaces in a macro variable list

Kurt_Bremser — Fri, 04 Jan 2019 18:55:46 GMT

Expand the

if first.county

with a do/end block, and have setting the fname and a put for the header line in it.

if first.county
then do;
  fname = .......;
  put "county,var1,var2,.....";
end;

The change of the value in fname will cause the current file to be closed and a new one to be opened.

Re: How to handle spaces in a macro variable list

lstockman — Fri, 04 Jan 2019 19:33:48 GMT

That worked perfectly. Thank you!

Re: How to handle spaces in a macro variable list

Toni2 — Thu, 30 Sep 2021 14:18:11 GMT

i used this. very smart solution, thanks 🙂