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 :DATE9. ;
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
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.
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.
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;
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
@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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.