topic DATES!!!! in SAS Programming

DATES!!!!

dereck255 — Wed, 14 Jun 2017 15:43:09 GMT

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;

Re: DATES!!!!

Astounding — Wed, 14 Jun 2017 15:51:43 GMT

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.

Re: DATES!!!!

dereck255 — Wed, 14 Jun 2017 15:59:48 GMT

To think that all I was missing this entire time was "d".....

Thank you!!!

Re: DATES!!!!

ballardw — Wed, 14 Jun 2017 17:03:32 GMT

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

Re: DATES!!!!

dereck255 — Wed, 14 Jun 2017 17:09:49 GMT

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

Re: DATES!!!!

ballardw — Wed, 14 Jun 2017 17:35:26 GMT

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