DATA Step, Macro, Functions and more

DATES!!!!

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

DATES!!!!

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;


Accepted Solutions
Solution
‎06-14-2017 11:59 AM
Super User
Posts: 5,516

Re: DATES!!!!

Posted in reply to dereck255

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


All Replies
Solution
‎06-14-2017 11:59 AM
Super User
Posts: 5,516

Re: DATES!!!!

Posted in reply to dereck255

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.

Contributor
Posts: 25

Re: DATES!!!!

Posted in reply to Astounding
To think that all I was missing this entire time was "d".....

Thank you!!!
Super User
Posts: 11,343

Re: DATES!!!!

Posted in reply to dereck255

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.

Contributor
Posts: 25

Re: DATES!!!!

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

Super User
Posts: 11,343

Re: DATES!!!!

Posted in reply to dereck255

dereck255 wrote:

Don't worry Smiley Happy 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.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 184 views
  • 1 like
  • 3 in conversation