topic Re: Macro loop date monthly seq in SAS Procedures

Macro loop date monthly seq

BETO — Tue, 16 Jun 2015 20:08:04 GMT

i have a macro that runs a seq for every day of a month here is the ode with the table ....how do I make it to where table one actually runs for every day

% macro run code (today);

%include "a:\password.sas";

proc sql:

cconnect to odbc (datasrc ='prod' user=******* password=*******);

create table one as select * from connection to odbc

( select

id,

admin_dat

from table

where admin_dat= &td.

);

quit;

%mend;

%macro runmail;

%do i=1 %to 30;

data _null_;

call symputx('td'," ' " ||put (mdy(4,&i.,2015),date.)||" 'd ");

run;

%put &td.;

%end;

%mend;

%runall;

Re: Macro loop date monthly seq

Tom — Tue, 16 Jun 2015 20:42:48 GMT

Assuming that you have some actual code that you want to run here is one way to generate each day of a month.

If you start with any day during the month, for example today is 2015-06-16, then you can using the INTNX() function to find the first and last day of the month.

%let today="16Jun2015"d ;

data _null_;

do day = intnx('MONTH',&today,0,'B') to intnx('MONTH',&today,0,'E');

put day= date9.;

end;

run;

Re: Macro loop date monthly seq

Astounding — Tue, 16 Jun 2015 20:47:09 GMT

Why pull each day separately? Why not pull the entire month of data, and figure out how to process it BY ADMIN_DAT?

Re: Macro loop date monthly seq

BETO — Wed, 17 Jun 2015 02:49:07 GMT

I Try that an when it's a day pull it works by grouping of site

site machine 1. Date1. Machine2. Date2

A A1. 06/15/15 15:20:00 a2 06/15/15 15:23:00

when I increase the range it puts in every instance of site together

A A1. 6/15/15 15:20:00 a2 06/15/15 15:23:00 a1 06/13/1512:00:00

history

what im doing is comparing a site that has machine that are brought down I determine if both machines are brought down at same time .... So I need tge dt format to get that break out...I fig the macro if it runs for every single day it would be a work around

Re: Macro loop date monthly seq

BETO — Wed, 17 Jun 2015 02:52:34 GMT

Yes its actual code could you give me any ideas how I can save each day seq or to auto append the data after if fin running an new seq starts ? Thanks

Re: Macro loop date monthly seq

Tom — Wed, 17 Jun 2015 11:23:10 GMT

So if you have a macro that does what you want you could use CALL EXECUTE() in the data step to call.

data _null_;

do day = intnx('MONTH',&today,0,'B') to intnx('MONTH',&today,0,'E');

call execute(cats('%mymacro(',day,')') ;

end;

run;

Now if your macro is like what you posted it will not work since the later calls will just overwrite the data generated by the earlier calls.

One way to deal with that is to accumulate the values into a new dataset. PROC APPEND is convenient for that.

%macro mymacro(day);

proc sql ;

....

create table next as .....

....

quit;

proc append base=all data=next force ;

run;

%mend mymacro;

Re: Macro loop date monthly seq

BETO — Wed, 17 Jun 2015 16:44:48 GMT

Hi Tom thanks for the assistance. ..on my table where I need to pull mo th worth data ...where do I add the macro I currently have a where admin date between start and end ...but to use your macro would it be admin date = ?

Re: Macro loop date monthly seq

Tom — Wed, 17 Jun 2015 16:55:00 GMT

Here is method to use to get a working macro.

1) Write the code that does what you want without any macro variables or macro definitions. This could be tricky in your case since you look to be using pass through SQL so the syntax for the SQL that is sent directly to the database needs to follow that databases rules and not SAS's sql rules.

Once you have step (1) working you can move to step (2).

2) Replace the variable parts with macro variable references and add a %LET to the top to set the macro variable value.

Once you have step(2) working you can move to step(3).

3) Wrap the code in a macro definition. Removing the %LET and adding a parameter to the macro definition. Add a call after the %mend so that you can test again with the same value.

Once you have step(3) working now you can work on the looping aspects. If you use the data step to loop make sure that the value that is being generated is in the right syntax to work for your macro.

For example if you are passing a date value to a SAS WHEre clause then you can either just pass the internal representation of that date (number of days since 1.1.1960) or a date literal. But if you are pushing that macro variable into the passthrough code to a database then you need to generate a date constant that the database will understand.

Re: Macro loop date monthly seq

BETO — Wed, 17 Jun 2015 16:58:28 GMT

I got it to work