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

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

ErikLund_Jensen
Rhodochrosite | Level 12

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;

 

Tom
Super User Tom
Super User

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

 

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Astounding
PROC Star

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.

Lohia
Calcite | Level 5
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..

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
  • 10733 views
  • 1 like
  • 6 in conversation