topic Re: Date macro variable not resolving in proc sql in SAS Programming

Date macro variable not resolving in proc sql

Lohia — Wed, 06 Sep 2017 12:49:37 GMT

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

Re: Date macro variable not resolving in proc sql

RW9 — Wed, 06 Sep 2017 13:01:46 GMT

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.

Re: Date macro variable not resolving in proc sql

ErikLund_Jensen — Wed, 06 Sep 2017 13:08:13 GMT

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;

Re: Date macro variable not resolving in proc sql

Tom — Wed, 06 Sep 2017 13:30:25 GMT

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

Re: Date macro variable not resolving in proc sql

PaigeMiller — Wed, 06 Sep 2017 13:53:29 GMT

@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.

Re: Date macro variable not resolving in proc sql

Astounding — Wed, 06 Sep 2017 13:56:33 GMT

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.

Re: Date macro variable not resolving in proc sql

Lohia — Thu, 07 Sep 2017 10:02:53 GMT

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..