How to handle spaces in a macro variable list

[lstockman]

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

[PaigeMiller]

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,~);

[lstockman]

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? 

[andreas_lds]

---

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

[Kurt_Bremser · Accepted solution]

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.

[lstockman]

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?

[Kurt_Bremser]

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.

[lstockman]

That worked perfectly. Thank you!

[PaigeMiller]

---

@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( )));

[lstockman]

Thank you @PaigeMiller!

[Toni2]

i used this. very smart solution, thanks 🙂

[novinosrin]

@PaigeMiller 's recommendation is neat. 

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