BookmarkSubscribeRSS Feed
BETO
Fluorite | Level 6

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;

8 REPLIES 8
Tom
Super User Tom
Super User

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;

BETO
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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;


BETO
Fluorite | Level 6

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 = ?

Tom
Super User Tom
Super User

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.

BETO
Fluorite | Level 6

I got it to work

Astounding
PROC Star

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

BETO
Fluorite | Level 6

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 1622 views
  • 1 like
  • 3 in conversation