DATA Step, Macro, Functions and more

Can't get macro to work

Reply
Frequent Contributor
Frequent Contributor
Posts: 83

Can't get macro to work

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;

Super User
Posts: 11,343

Re: Can't get macro to work

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.

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Can't get macro to work

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;

Super User
Posts: 5,429

Re: Can't get macro to work

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.

Data never sleeps
Frequent Contributor
Frequent Contributor
Posts: 83

Re: Can't get macro to work

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).

Super User
Posts: 19,805

Re: Can't get macro to work

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;

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Can't get macro to work

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;

Occasional Contributor
Posts: 6

Re: Can't get macro to work

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;

                      <do important things>

               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.

Super User
Super User
Posts: 7,955

Re: Can't get macro to work

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;

Ask a Question
Discussion stats
  • 8 replies
  • 291 views
  • 0 likes
  • 6 in conversation