BookmarkSubscribeRSS Feed
Ody
Quartz | Level 8 Ody
Quartz | Level 8

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;

8 REPLIES 8
ballardw
Super User

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.

Ody
Quartz | Level 8 Ody
Quartz | Level 8

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;

LinusH
Tourmaline | Level 20

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
Ody
Quartz | Level 8 Ody
Quartz | Level 8

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

Reeza
Super User

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;

Ody
Quartz | Level 8 Ody
Quartz | Level 8

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;

optimist
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

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
  • 8 replies
  • 1133 views
  • 0 likes
  • 6 in conversation