How do I pass a date macro variable as a parameter

[jb9977]

I am trying to pass a date macro variable into another macro variable and unable to do that.  Here is the code that I am using

LET TODAY = %sysfunc(TODAY(),DATE9.);

data dates;

lastwk=intnx('week', "&TODAY."D, -2);

last7wk=intnx('week', "&TODAY."D, -8);

lastSun= intnx('week.1', lastwk , 1);

last7Sun= intnx('week.1', last7wk , 1);

format lastwk date9.;

format last7wk date9.;

format last7Sun date9.;

format lastSun date9.;

call symput('lastwk',lastwk);

call symput('lastwk_Sun',lastwk);

call symput('last7Sun',last7Sun);

call symput('lastSun',lastSun);

run;

%macro test(V1=,V2=,V3=); 

select *

     , case

     when No in (1648,1653,1612, 1650)then 'Express'

     when No in  (1638,1634,1637,1661) then 'Non-Express'

else 'none' End as Mode

from (

                     SELECT *

                     FROM XXXX.ABCD t1

                      WHERE t1.ACTION_DATE>=&V1. and  t1.ACTION_DATE< &V2. and

                           t1.No in (1648,1653,1612, 1650, 1638,1634,1637,1661,1974,1975,1976)

                           and LEVEL_CD='&V3'

                 ) T1    

%mend;

proc sql;

create table table_2 as 

select *

     From

     (%test(V1=&last7Sun.,V2==&lastSun.,V3=2) )T2       

; 

Quit;

Thanks in Advance.

[Reeza]

Ok...let's say I don't want to run your code, can you please explain what issue you're having. 

Off the bat, it looks like you may be using macro variables with single quotes, which won't work.

and LEVEL_CD='&V3'

What formats/ types are your data in the data base. And does your code work before you turned it into a macro? If so, what did that look like?

You also used double == in your macro call which could be incorrect, not sure how it would be interpreted.

[snoopy369]

What does "unable to" mean?

In general, if you're staying within SAS-land, I would not format it nicely; so remove the `,DATE9.` from your %SYSFUNC. Leave it as the bare return value (a number). Then you don't have to do the "&DATE"d stuff, you can just treat it as any other number, unless you actually need to print it nicely in which case you always can use %SYSFUNC(PUTN(...)) to get it printed nicely.

If your XXXX library is in some other DBMS, though, like Oracle or SQL Server, you may have some issues filtering; you should specify in more detail in your question.

[ballardw]

Why

%LET TODAY = %sysfunc(TODAY(),DATE9.);

data dates;

lastwk=intnx('week', "&TODAY."D, -2);

last7wk=intnx('week', "&TODAY."D, -8);

Your %let statement calls the datastep function today(). Since you are running a data step why not run it in the data step?????

data dates;

lastwk=intnx('week', today(), -2);

last7wk=intnx('week', Today(), -8);

You may also want

lastSun= intnx('week', today(),-2,"B"); with your result.

You should post code and/log results (code with the errors) into a code box opened with the forum {I} menu icon.

If you aren't getting errors then show the results you are getting and the expected result.

[s_lassen]

You have a couple of problems with the definition and use of your macro variables: The macro variable V3 does not get resolved when put in single quotes ('&V3'), use double quotes: "&V3".

And the call of the %TEST macro has an equal sign too many for V2, "V2==&lastSun".

Other than that, the code looks OK to me, but I have not tested.

I would use the SYMPUTX routine instead of SYMPUT, though, because you do not get notes about conversion to character, and the value gets automatically trimmed. 

[Quentin]

The only errors I see are not having a % on the LET TODAY= statement at the stop, and having single quotes around the macro variable LEVEL_CD='&V3'.

The v2==&lastSun may be correct, the macro generates: ACTION_DATE< = 21184 .  I can't remember if the space between the less than sign and equals sign is a problem. If it is, you can remove the space in the macro

I didn't actually read the code to see if the logic made sense, but I did run it.

[mkeintz · Accepted solution]

I almost always prefer to specify date macro values in  ddMMMYYYY format (i.e. like   12JAN2018). That way it can be part of a date literal in a sas program, as in:

 %let mydate=01JAN2018;

Then you can use

     data _null_;

       start_date="&mydate"d;

       .....

Now, if you want the current date, and if your sas session is newly started (i.e. not left over from yesterday), then just use the automatic macro variable &sysdate9,   i.e.

   %let mydate=&sysdate9;

    .....