DATA Step, Macro, Functions and more

Date macro variable not resolving in proc sql

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Date macro variable not resolving in proc sql

Hi Guys,

 

Need your help to resolve the date macro variable value,

 

Data _null_;
DATE1='01JAN2016'd;
CALL SYMPUT ('RPTDT',"'" || PUT(DATE1,YYMMDD10.) ||"'");
RUN;
%PUT &RPTDT ;

 


DATA TEST;
INPUT CHARV : $1. DATES Smiley Very HappyATE9. ;
FORMAT DATES DATE9.;
DATALINES;
A 01FEB2016
B 01DEC2016
C 03FEB2015
D 06JUN2014
E 09JUL2017
;
RUN;

PROC PRINT DATA=TEST ; RUN;

PROC SQL;
CREATE TABLE TNEW AS 
SELECT * FROM TEST
WHERE DATES >= &RPTDT.
;
QUIT;
RUN;

 

it's not working correctly, Please suggest solution.

 

I need to use this date macro variable in select and where statement.

 

Thanks in advance


Accepted Solutions
Solution
‎09-07-2017 05:58 AM
Super User
Super User
Posts: 7,997

Re: Date macro variable not resolving in proc sql

Please avoid coding all in upcase, its makes code so much harder to read.  You can use the {i} above the post area to post code, it then keeps the indentaion and such like.  Macro and macro variables are all text, so you need to think about text, not numbers (which dates are), which is why its only good for text generation.  Something like:

%let rptdt=01JAN2016;

data test;
...;
run;

proc sql;
  create table TNEW as
  select  *
  from    TEST
  where   DATES >= "&rptdt."d;
quit;

So the text from the macro variable gets resolved inside the normal use for a date literal.  

View solution in original post


All Replies
Solution
‎09-07-2017 05:58 AM
Super User
Super User
Posts: 7,997

Re: Date macro variable not resolving in proc sql

Please avoid coding all in upcase, its makes code so much harder to read.  You can use the {i} above the post area to post code, it then keeps the indentaion and such like.  Macro and macro variables are all text, so you need to think about text, not numbers (which dates are), which is why its only good for text generation.  Something like:

%let rptdt=01JAN2016;

data test;
...;
run;

proc sql;
  create table TNEW as
  select  *
  from    TEST
  where   DATES >= "&rptdt."d;
quit;

So the text from the macro variable gets resolved inside the normal use for a date literal.  

Contributor
Posts: 34

Re: Date macro variable not resolving in proc sql

Hi Lohia

The problem is that your macro variable contains a text string with year, month and date, while your input data contains a sas date value. Just store the SAS date value.

 

Data _null_;
DATE1='01JAN2016'd;
CALL SYMPUTX('RPTDT',DATE1);
RUN;

 


%PUT &RPTDT ;

DATA TEST;
INPUT CHARV : $1. DATES DATE9. ;
FORMAT DATES DATE9.;
DATALINES;
A 01FEB2016
B 01DEC2016
C 03FEB2015
D 06JUN2014
E 09JUL2017
;
RUN;

 

Super User
Super User
Posts: 7,080

Re: Date macro variable not resolving in proc sql

[ Edited ]

It what way is not working correctly?  Are you getting error messages? Just not matching the values you expect?

 

Your first step generated a character string literal with a date value formatted into YYYY-MM-DD format.

2933  %PUT &RPTDT ;
'2016-01-01'

So that will only work if the variable you are comparing to is also a character variable and its contents also look like date values with the same format.  

 

If your variable has actual date values in it then you need to compare to a date literal like 

"01JAN2016"d

or an actual number of days since 01JAN1960 like

20454

 

Trusted Advisor
Posts: 1,934

Re: Date macro variable not resolving in proc sql

[ Edited ]

Lohia wrote:

Hi Guys,

 

Need your help to resolve the date macro variable value,

 

Data _null_;
DATE1='01JAN2016'd;
CALL SYMPUT ('RPTDT',"'" || PUT(DATE1,YYMMDD10.) ||"'");
RUN;
%PUT &RPTDT ;


Others have given you good advice. However it is completely unnecessary to use the format, this actually makes your life harder. Instead, use

 

data _null_;
    call symputx('rptdt','01JAN2016'd);
run;

and then later

 

proc sql;
    create table tnew as select * from test where dates >= &rptdt;
quit;

In general, you don't need date formats or datetime formats for coding. In fact, they are unnecessary (although there probably are exceptions). The place they are needed is to display results, for example in a TITLE or LABEL statements, or the output from some PROC. 

Super User
Posts: 5,518

Re: Date macro variable not resolving in proc sql

While you have many accurate suggestions, the simplest one hasn't appeared yet.

 

This code can be replaced:

 

Data _null_;
DATE1='01JAN2016'd;
CALL SYMPUT ('RPTDT',"'" || PUT(DATE1,YYMMDD10.) ||"'");
RUN;

 

Instead, simply use:

 

%let rptdt = '01JAN2016'd;

 

Then the rest of the code should be fine.

Occasional Contributor
Posts: 18

Re: Date macro variable not resolving in proc sql

Posted in reply to Astounding
Thank you everyone, i tried RWS method its working.
Initially i also tried the same but forgot to remove quotes from the date
Thats why when i'm applying the right method its showing me the error like not resolved "'2016-01-01'".

Thanks a lot again..
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 105 views
  • 1 like
  • 6 in conversation