BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jb9977
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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;

    .....

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

6 REPLIES 6
Reeza
Super User

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
Barite | Level 11
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
Super User

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
Meteorite | Level 14

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
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
mkeintz
PROC Star

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;

    .....

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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
  • 6 replies
  • 49139 views
  • 3 likes
  • 7 in conversation