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

I will always and forever struggle with dates in SAS.

 

I have a report that has a column called "Date of Last Review" - we'll call this DOLR for short. When I import this into SAS, it's brought in as, for example, 03MAR2017 - which is perfect.

 

I have a Macro for DOLR - so that when the next report is run, when DOLR is "" that the DOLR should be what I enter in the for the Macro. I'm having huge issues with this simple task. SAS was importing DOLR as a numeric when I wanted it as a character. Tried to fix it, didn't work.

 

So I'll have it stay as a numeric. When I let my macro:

%let DOLR=18/03/2017;

 

my output for DOLR is

01JAN1960

 

What gives?? Why is SAS so insane when it comes to dates?? I've tried various iterations of 03Mar2017 in the let macro statement and nothing comes even remotely close to the date.

 

Full code:

 

%let DOLR=18/03/2017;

 

PROC IMPORT DATAFILE="/sp/ClinOps/send/Excel Files/Previous.xls"
     OUT=PreviousIn
     DBMS=XLS
     REPLACE;
     Sheet="Final_eTMF";
RUN;


data PreviousOut (drop='Paper Received Date'n);
length CodeName $ 20;
set PreviousIn;
run;

 

proc sort data=PreviousOut;
by 'TMF Obj ID'n;
run;

 

proc sort data=Final;
by 'TMF Obj ID'n;
run;

 

data mPrevious;
merge PreviousOut Final;

by 'TMF Obj ID'n;
If Reviewed="" then Reviewed="n";
run;

 

data ToReview;
set mPrevious;
if Reviewed="y" and DOLR="" then DOLR=&DOLR;
drop X Y;
run;

 

proc sort data=ToReview;
by CodeName Zone Section Artifact;
run;

 

proc export data=ToReview
 outfile="/sp/ClinOps/send/Excel Files/To Review eTMF.xls"
 label dbms=xls replace;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

This problem is only partly a matter of dates.  It's also a matter of understanding how macro language works.  Your code generates a statement along these lines:

 

if .... then DOLR = 18/03/2017;

 

In a DATA step, that's just 18 divided by 3, divided by 2017.  The answer is close enough to zero, so you are getting the date that matches the number zero (which is January 1, 1960).

 

Within knowing what's in the data set FINAL ... it's coming in from unknown sources as far as I can see ... it's difficult to find a solution. It might be any of these (or even somthing slightly similar):

 

%let DOLR = 18MAR2017;

 

%let DOLR = "18MAR2017"d;

 

But that should at least point you in the right direction.

View solution in original post

5 REPLIES 5
Astounding
PROC Star

This problem is only partly a matter of dates.  It's also a matter of understanding how macro language works.  Your code generates a statement along these lines:

 

if .... then DOLR = 18/03/2017;

 

In a DATA step, that's just 18 divided by 3, divided by 2017.  The answer is close enough to zero, so you are getting the date that matches the number zero (which is January 1, 1960).

 

Within knowing what's in the data set FINAL ... it's coming in from unknown sources as far as I can see ... it's difficult to find a solution. It might be any of these (or even somthing slightly similar):

 

%let DOLR = 18MAR2017;

 

%let DOLR = "18MAR2017"d;

 

But that should at least point you in the right direction.

dereck255
Fluorite | Level 6
To think that all I was missing this entire time was "d".....

Thank you!!!
ballardw
Super User

@dereck255 wrote:
To think that all I was missing this entire time was "d".....

Thank you!!!

NO! You need to do more than just add 'd'.

Using

%let DOLR='18/03/2017'd;

WILL not work. The literal values when used by SAS must be in the form of ddMONyy or ddMONyyyy,

One major reason is there are so many ways people use dates that 10/03/2017 could be 03 OCT  or 10 MAR with added confusion when no separator is included: 010203 could be 1 Feb 2003, 2 Jan 2003, 3 Feb 2001.

dereck255
Fluorite | Level 6

Don't worry 🙂 I already changed it to:"18MAR2017",d

ballardw
Super User

@dereck255 wrote:

Don't worry 🙂 I already changed it to:"18MAR2017",d


We get a question, or an "it didn't work" to a suggestion when someone uses a '01/05/2015'd about twice a week or so.

Often we have provided an example that shows
data junk;

     datevalue = '01MAR2017'd;

run;

 

And then the OP comes back saying

data junk;

    datevalue = '03/01/2017'd;

run;

has a missing value.

 

So I just thought I'd head that if in case of a misunderstanding.

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
  • 5 replies
  • 1209 views
  • 1 like
  • 3 in conversation