BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
dan999
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

: 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

7 REPLIES 7
Reeza
Super User

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;

dan999
Fluorite | Level 6

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.

ballardw
Super User

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.

Reeza
Super User

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;

dan999
Fluorite | Level 6

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.

art297
Opal | Level 21

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

dan999
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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