My data has a column called program_name and I want to generate a report for each program_name. I have a proc sql query that gets a distinct list of the program names. Some of the program names consist of more than one word. My code works for the program names that are only one word but fails for the program names of two or more words. How do I get it to work for program names of more than one word? Thanks.
data prg_names1;
input @1 program_name $22. @23 did 3.;
datalines;
math 301
english 302
social studies 303
environmental science 304
biology 305
;
run;
OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MPRINT MLOGIC;
%macro exportprograms(program);
proc sql;
create table &program as
select *
from prg_names1
where program_name = "&program";
run;
proc export data=&program
outfile="&destn/&reportnm._&program..xlsx"
dbms=xlsx replace;
run;
%mend exportprograms;
/*%exportprograms(math);*/
data _null_;
set prg_names1;
/* length program $ 50;*/
/* call symputx('program',program_name);*/
/* call execute('%exportprograms(program_name)');*/
call execute('%exportprograms(program=' || program_name || ')' );
run;
: If your goal was just to create the Excel files, why create SAS datasets as well? I think your macro will work fine as follows:
%macro exportprograms(program);
proc export data=prg_names1 (where=(program_name = "&program"))
outfile="&destn/&reportnm._&program..xlsx"
dbms=xlsx replace;
run;
%mend exportprograms;
data _null_;
set prg_names1;
call execute('%exportprograms(program=' || program_name || ')' );
run;
Let's look at a word with a space in it, ie Social Studies
Your Code that SAS would Generate, which would have showed in your log with the mprint and symbolgen options.
That isn't valid SAS code, so I would consider how to deal with that.
Create a new macro variable that will meet the SAS naming conventions by eIther remove spaces or replace with underscore and pass that to the program instead.
You could also change the options to validvarname=any but I wouldn't recommend that.
proc sql;
create table Social Studies as
select *
from prg_names1
where program_name = "Social Studies";
run;
proc export data=Social Studies
outfile="&destn/&reportnm._Social Studies..xlsx"
dbms=xlsx replace;
run;
I'm not sure what you mean by "isn't valid SAS code". Could you explain further. It works for programs with only one word. Your suggestion to remove spaces or add an underscore wouldn't work because the query in the macro wouldn't match a program with those new program names. Unless, I change the program names in the result set first. I've thought of that but I was hoping there was a way to do this without resorting to that.
Data set names in SAS are not allowed to have spaces. Check the online help for "Rules for SAS Data Set Names, View Names, and Item Store Names". A simple fix is to replace the spaces in those names with the _ character.
You should have been getting error messages in log with your attempted syntax.
The first is what your code is providing the equivalent of:.
Do you see the error now?
proc sql;
create table female only as
select *
from sashelp.class
where sex = "F";
run;
proc sql;
create table female_only as
select *
from sashelp.class
where sex = "F";
run;
Yes I realize that. I was looking for a way around it. I guess there isn't one. I went back to the full resultset and added a column that takes out the spaces. It works now. Thanks.
: If your goal was just to create the Excel files, why create SAS datasets as well? I think your macro will work fine as follows:
%macro exportprograms(program);
proc export data=prg_names1 (where=(program_name = "&program"))
outfile="&destn/&reportnm._&program..xlsx"
dbms=xlsx replace;
run;
%mend exportprograms;
data _null_;
set prg_names1;
call execute('%exportprograms(program=' || program_name || ')' );
run;
I wondered last night if there was a way to put a where clause in proc export but then I read so many other things about other ways of doing it that I forgot about it. I like that better. Thanks.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.