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;
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.
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 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.
Don't worry 🙂 I already changed it to:"18MAR2017",d
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.