DATA Step, Macro, Functions and more

help calling macro from data step

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

help calling macro from data step

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;


Accepted Solutions
Solution
‎10-17-2014 05:45 PM
PROC Star
Posts: 7,474

Re: help calling macro from data step

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

View solution in original post


All Replies
Super User
Posts: 19,815

Re: help calling macro from data step

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;

Contributor
Posts: 37

Re: help calling macro from data step

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.

Super User
Posts: 11,343

Re: help calling macro from data step

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.

Super User
Posts: 19,815

Re: help calling macro from data step

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;

Contributor
Posts: 37

Re: help calling macro from data step

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.

Solution
‎10-17-2014 05:45 PM
PROC Star
Posts: 7,474

Re: help calling macro from data step

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

Contributor
Posts: 37

Re: help calling macro from data step

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 265 views
  • 1 like
  • 4 in conversation