topic help calling macro from data step in SAS Programming

help calling macro from data step

dan999 — Fri, 17 Oct 2014 15:27:51 GMT

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;

Re: help calling macro from data step

Reeza — Fri, 17 Oct 2014 15:34:58 GMT

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;

Re: help calling macro from data step

dan999 — Fri, 17 Oct 2014 17:37:04 GMT

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.

Re: help calling macro from data step

ballardw — Fri, 17 Oct 2014 17:51:41 GMT

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.

Re: help calling macro from data step

Reeza — Fri, 17 Oct 2014 18:04:52 GMT

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;

Re: help calling macro from data step

dan999 — Fri, 17 Oct 2014 20:46:47 GMT

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.

Re: help calling macro from data step

art297 — Fri, 17 Oct 2014 21:45:51 GMT

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

Re: help calling macro from data step

dan999 — Fri, 17 Oct 2014 21:58:48 GMT

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.