topic Can't get macro to work in SAS Programming

Can't get macro to work

Ody — Thu, 09 Apr 2015 14:34:12 GMT

Hello,

I'm having some trouble with this macro and would appreciate another set of eyes.

I'm trying to create an excel workbook where every sheet is data from my Emp_History1 table equal to the dates in my PERIOD_DT table. I'm not getting any errors when this runs but it's not doing what i would expect. Appreciate any help, thanks.

proc sql noprint;

select min(PERIOD_DT) format = date9. into: min_date

from Zwork.period_Dates;

quit;

proc sql noprint;

select max(PERIOD_DT)format = date9. into: max_date

from Zwork.period_Dates;

quit;

options mlogic mprint;

%macro test (first=&min_date, last=&max_date);

%do date = &first %to &last;

data work.table_&date;

set zwork.Emp_History1;

if PERIOD_DT in (&date);

run;

proc export data = work.table_&date

outfile = "W:\_TD9756_WAPPRIB00001040_\test.xlsx"

DBMS = EXCEL replace;

sheet = table_&date;

run;

%end;

%mend test;

Re: Can't get macro to work

ballardw — Thu, 09 Apr 2015 14:41:57 GMT

If PERIOD_DT is a SAS date valued variable, which is implied by your use of the date9. format, your comparison of

if PERIOD_DT in (&date); is failing as &date resolves to something like 01Jan2015 NOT "01Jan2015"d;

use

if PERIOD_DT in ("&date"d);

You can also build both the min and max in a single pass.

Re: Can't get macro to work

RW9 — Thu, 09 Apr 2015 15:03:20 GMT

Hi,

Something like (note not tested):

proc sql;

create table TEMP as

select MIN(PERIOD_DT) as MN,

MAX(PERIOD_DT) as MX

from ZWORK.PERIOD_DATES;

quit;

data _null_;

set TEMP;

do i=mn to mx;

call execute('proc export data=zwork.emp_history1 (where=(period_dt='||strip(put(i,best.))||'))

outfile="...\file_'||strip(put(i,best.))||'.xlsx" dbms=excel replace;

run;');

end;

run;

Re: Can't get macro to work

Ody — Thu, 09 Apr 2015 15:08:50 GMT

I always forget about being able to do multiple "into :'. Thanks for the reminder.

I've changed my program to the following but I still can't get it to work. I don't get an error message or anything:

proc sql noprint;

select

min(PERIOD_DT) format = date9.,

max(PERIOD_DT)format = date9.

into: min_date, : max_date

from Zwork.period_Dates;

quit;

options mlogic mprint;

%macro test (first=&min_date, last=&max_date);

%do date = &first %to &last;

data work.table_&date;

set zwork.Emp_History1;

if PERIOD_DT in ("&date"d);

run;

proc export data = work.table_&date

outfile = "W:\_TD9756_WAPPRIB00001040_\test.xlsx"

DBMS = EXCEL replace;

sheet = table_&date;

run;

%end;

%mend test;

Re: Can't get macro to work

LinusH — Thu, 09 Apr 2015 15:19:25 GMT

But you are not calling the macro?

It's a bit odd that you have data driven default parameters when creating the macro. Could work. I take it that you will recreate the macro each run.

But if you do, no need to use parameters, you could use the dates right off within the macro.

Re: Can't get macro to work

Ody — Thu, 09 Apr 2015 15:32:41 GMT

I am calling the macro. The only message the log gives me is "Remote submit to Server complete". I'm not getting any errors, etc. It's a little frustrating.

I'm open to other solutions. I'm not totally comfortable with macros so I went with what i thought was easiest for me (compliments of the Macro 1 course I took recently).

Re: Can't get macro to work

Reeza — Thu, 09 Apr 2015 15:52:41 GMT

1. Have you defined the full macro program on the server as well as the macro call?

2. What part isn't working as expected? Usually by default I wouldn't expect the a server to have access to the W drive, if that's your local network though it may.

I'm not sure I'd expect it to work anyways - what do first and last resolve to? You most likely need to convert it to the appropriate date structure.

%do date = &first %to &last;

data work.table_&date;

set zwork.Emp_History1;

if PERIOD_DT in ("&date"d);

run;

Re: Can't get macro to work

Ody — Thu, 09 Apr 2015 16:33:41 GMT

Thanks for all the replies.

I was able to get it working by changing the following:

%macro test;

%do date = &min_date %to &max_date;

Re: Can't get macro to work

optimist — Thu, 09 Apr 2015 17:16:45 GMT

Following Reeza's comments, you really haven't provided us much information. But looking over your post, it may be a problem with how you are handling the dates.

In your macro %do loop, the begin and end values resolve to character representations of the date (e.g. 31MAY2009). So SAS see this as something like:

DO i=31MAY2009 TO 20JUN2009;

END;

How does SAS count from these values? I would suggest storing the dates as SAS dates in the macro variables and iterate over those. From the program in your first post, it looks like the variable PERIOD_DT is already stored as a SAS (numerical) date, so this should work. So just drop the FORMAT = date9. in the select statement.

<LATER.After your comment> I'm guessing you changed something else in the format in how you stored the dates, so that your were iterating over numbers rather than something else. For everyone who tries to learn from these postings, it would be good if you included the (full) version of the code that worked for you.