MMDDYY10. format turning all dates into 01/01/1960

Reply
Frequent Contributor
Posts: 131

MMDDYY10. format turning all dates into 01/01/1960

Does anyone know why the MMDDYY10. format turned all my date variables to the date constant 01/01/1960?

I applied the format in the first place because I had created a list of date variables, where many were showing up in PROC PRINT as a decimal; I was surprised to see the decimals because my understanding was SAS would read them as a whole number of days from the date 01/01/1960.

Super User
Posts: 19,850

Re: MMDDYY10. format turning all dates into 01/01/1960

Posted in reply to Maisha_Huq

I usually see that when I have a datetime variable, not a date variable.

Try using a dtdate9. format on the variable and see what shows up.

Otherwise, provide more info, specifically what your original unformatted data looks like. 

Frequent Contributor
Posts: 131

Re: MMDDYY10. format turning all dates into 01/01/1960

Thanks, Reeza!

Even when I apply the dtdate9. format, though, all my dates turn into 01/01/1960.

What I was doing is this:  For a number of datasets, I made date variables such that

if var_a=1 then date01=01/1/2012; else date01= .;

if var_b=1 then date02=03/15/2013; else date02=.;

.

.

.

The date variables, however, show up in unexpected formats.  Some are in decimals while others appear to be the number of days since 01/01/1960.  Not sure where the problem is.

Thank you!

Super Contributor
Posts: 644

Re: MMDDYY10. format turning all dates into 01/01/1960

Posted in reply to Maisha_Huq

Maisha

If you have correctly copied your code the problem lies with the way you have expressed the dates.

Sas interprets date01=01/1/2012 to be 1 divided by 1 divided by 2012, a number close to zero.  Hence when formatted as a date it is shown as zero days from 1 Jan 1960, the 'zero' point for SAS dates.

You need to use a SAS date literal, which is a DATE7., DATE9. or DATE11. expression enclosed in quotes and followed by a D.

Try

if var_a=1 then date01='01JAN2012'D ; else date01= .;


Richard

Frequent Contributor
Posts: 131

Re: MMDDYY10. format turning all dates into 01/01/1960

Posted in reply to RichardinOz

Hi Richard:

Thanks so much - that's really helpful.  Since the code is for almost 800 date variables, though, do you know if there's any way to do this without going back through my entries for the dates and changing them so they're either 01/01/2012 or 01JAN2012?

When I enclose the dates as they're written now (many not in a SAS date literal format) in quotes and the 'd', that doesn't solve it.

Thank you!

Super Contributor
Posts: 644

Re: MMDDYY10. format turning all dates into 01/01/1960

Posted in reply to Maisha_Huq

You are correct that the expression '01/01/2012'D will not be interpreted as a SAS date. 

You could adopt pradeepalankar's solution by copying and pasting the input("  and ",mmddyy10.) bits before and after all your dates.  Be aware that this is a less efficient solution for a large dataset because the input function needs to be evaluated for each condition each time, whereas the date literals are evaluated only once.  If you have more than a million rows you will notice the difference.

Richard

Super Contributor
Posts: 644

Re: MMDDYY10. format turning all dates into 01/01/1960

Posted in reply to RichardinOz

And here is a way to change your code to date literals.  Simply paste all your if statements between Datalines4 and ;;;;

and run - then copy the new if condition from the log and paste them into your original code.  This only has to be done once.

Warning - untested code!  So make sure you have a backup copy of your original code. 

Data _NULL_ ;

  Infile Datalines ;

  Input ;

  Length Date_Lit $ 12 ;

  p1 = find("=", _Infile_, 20) ; /* Second = */

  p2 = find(";", _Infile_) ; /* First ; */

  Part_A = Substr(_Infile_, 1, p1) ;

  Part_C = Substr(_Infile_, p2, 20) ;

  Date_MMDDYY = Substr(_Infile_, p1+1, p2-p1-1) ;

  Date_Date9 = Put(Input(Date_MMDDYY, MMDDYY10.), Date9.) ;

  Part_B = catt("'", Date_Date9, "'D") ;

  Result = catt(Part_A, Part_B, Part_C) ;

  Put Result ;

Datalines4 ;

if var_a=1 then date01=01/1/2012; else date01= .;

if var_b=1 then date02=03/15/2013; else date02=.;

...

;;;;

Hope this works

Richard

Super User
Posts: 19,850

Re: MMDDYY10. format turning all dates into 01/01/1960

Posted in reply to Maisha_Huq

Can you post some of what your code looks like, with the 800 variables. In general that sounds like a bad way to code something.

Frequent Contributor
Posts: 131

Re: MMDDYY10. format turning all dates into 01/01/1960

Hi Reeza:

Sure!  Some of  my code is below:

In the code, I am match-merging a base dataset with about 30 datasets (each dataset contains info on different set of clients and their service/program usage; each dataset focuses on a different kind of service/program) in their own data step.  So, the list of variables to keep within each data step is different and the date variables each have only one value or missing.

It'd certainly be helpful if you let me know how efficient/not this system is.

Thank you!!

--

PROC SORT data=metricdata; BY last_name first_name; RUN;
PROC SORT data=_2013enrich_dup; BY last_name first_name; RUN;
DATA _2013enrich_dup_master;
MERGE metricdata (in=metric_)
_2013enrich_dup (in=_2013enrich_dup_ KEEP=last_name first_name
Participated_in_Enrichment_Prog1
Participated_in_Enrichment_Prog2
Participated_in_Enrichment_Prog3
Participated_in_Enrichment_Prog4
Participated_in_Enrichment_Prog5
Participated_in_Enrichment_Progr);
BY last_name first_name;

in_metric = metric_ ;
in__2013enrich_dup = _2013enrich_dup_ ;

if Participated_in_Enrichment_Prog1 =1 then date01=input("07/01/2013", mmddyy10.) ; else date01= . ;
if Participated_in_Enrichment_Prog2 =1 then date02=input("08/01/2013", mmddyy10.) ; else date02= . ;
if Participated_in_Enrichment_Prog3 =1 then date03=input("09/01/2013", mmddyy10.) ; else date03= . ;
if Participated_in_Enrichment_Prog4 =1 then date04=input("10/01/2013 ", mmddyy10.) ; else date04= . ;
if Participated_in_Enrichment_Prog5 =1 then date05=input("11/01/2013 ", mmddyy10.) ; else date05= . ;
if Participated_in_Enrichment_Progr =1 then date06=input("12/01/2013 ", mmddyy10.) ; else date06= . ;

if metric_=1 and _2013enrich_dup_=1 then output _2013enrich_dup_master;

drop Participated_in_Enrichment_Prog1
Participated_in_Enrichment_Prog2
Participated_in_Enrichment_Prog3
Participated_in_Enrichment_Prog4
Participated_in_Enrichment_Prog5
Participated_in_Enrichment_Progr;
RUN;

PROC SORT data=metricdata; BY last_name first_name;RUN;
PROC SORT data=_2014enrich_dup;BY last_name first_name;RUN;
DATA _2014enrich_dup_master;
MERGE metricdata (in=metric_)
_2014enrich_dup (in=_2014enrich_dup_ KEEP=last_name first_name
Jan_Participated_in_Enrichment_P
Feb_Participated_in_Enrichment_P
Mar_Participated_in_Enrichment_P
);
BY last_name first_name;

in_metric = metric_ ;
in__2014enrich_dup = _2014enrich_dup_ ;
if Jan_Participated_in_Enrichment_P = 1 then date07= input("01/01/2014", mmddyy10.) ; else date07=.;
if Feb_Participated_in_Enrichment_P = 1 then date08= input("02/01/2014", mmddyy10.) ; else date08=.;
if Mar_Participated_in_Enrichment_P = 1 then date09= input("03/01/2014", mmddyy10.) ; else date09=.;

if metric_=1 and _2014enrich_dup_=1 then output _2014enrich_dup_master;
drop Jan_Participated_in_Enrichment_P
Feb_Participated_in_Enrichment_P
Mar_Participated_in_Enrichment_P;
RUN;

Frequent Contributor
Posts: 106

Re: MMDDYY10. format turning all dates into 01/01/1960

Posted in reply to Maisha_Huq

try this,

if num_var1=1 then date01=input("01/1/2012",mmddyy10.); else date01= .;

if num_var2=2 then date02=input("03/15/2013",mmddyy10.); else date02=.;

Ask a Question
Discussion stats
  • 9 replies
  • 2331 views
  • 9 likes
  • 4 in conversation