topic Re: How do I pass a date macro variable as a parameter in SAS Programming

How do I pass a date macro variable as a parameter

jb9977 — Thu, 11 Jan 2018 22:18:15 GMT

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.

Re: How do I pass a date macro variable as a parameter

Reeza — Thu, 11 Jan 2018 22:23:36 GMT

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.

Re: How do I pass a date macro variable as a parameter

snoopy369 — Thu, 11 Jan 2018 22:23:54 GMT

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.

Re: How do I pass a date macro variable as a parameter

ballardw — Fri, 12 Jan 2018 00:11:31 GMT

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.

Re: How do I pass a date macro variable as a parameter

s_lassen — Fri, 12 Jan 2018 12:36:14 GMT

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.

Re: How do I pass a date macro variable as a parameter

Quentin — Fri, 12 Jan 2018 13:36:51 GMT

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.

Re: How do I pass a date macro variable as a parameter

mkeintz — Sat, 13 Jan 2018 05:23:53 GMT

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;

.....