Convert best12 to date

[merveayibogan]

Hello, I have an excel file named crspoutput that looks like this:

Model	permno	evtdate	cret	car	bhar	nrets_est
Market-Adjusted Model	10026	20170103	-0.0193	-0.02369	-0.02414	100
Market-Adjusted Model	10026	20170817	-0.01316	-0.01357	-0.01389	100
Market-Adjusted Model	10104	20170417	0.012635	0.000749	0.000795	100

When I check the formats, I see that evtdate is numeric in Best12. format with len 8, how can I convert  this to a num len 8 date9. format ?

Many thanks

[PaigeMiller]

evtdate1 = input(put(evtdate,8.),yymmdd8.);
format evtdate1 date9.;

[merveayibogan · Accepted solution]

Hello, thank you very much for your response. That didn't work but this worked:

data new data;
set olddata; 
if not missing(evtdate) then do;
year = int(evtdate / 10000); 
month = int((evtdate - year * 10000) / 100); 
day = mod(evtdate, 100);
put year= month= day= evtdate=;
new_date_variable = mdy(month, day, year);

format new_date_variable date9.;
end;
run;

[Tom]

Glad you got it to work.

But note that any value of EVTDATE that would could be split by your logic into separate year, month and day values that would also work when converted to a string using PUT() and then converted to a date value using INPUT().

Try it

data new data;
  set olddata; 
  if not missing(evtdate) then do;
    year = int(evtdate / 10000); 
    month = int((evtdate - year * 10000) / 100); 
    day = mod(evtdate, 100);
    new_date_variable = mdy(month, day, year);
    new_date_variable2 = input(put(evtdate,z8.),yymmdd8.);
    format new_date_variable new_date_variable2  date9.;
    put  (evtdate year month day new_date_variable new_date_variable2) (=);
  end;
run;

[PaigeMiller]

---

@merveayibogan wrote:

Hello, thank you very much for your response. That didn't work but this worked:

data new data;
set olddata; 
if not missing(evtdate) then do;
year = int(evtdate / 10000); 
month = int((evtdate - year * 10000) / 100); 
day = mod(evtdate, 100);
put year= month= day= evtdate=;
new_date_variable = mdy(month, day, year);

format new_date_variable date9.;
end;
run;

---

The code I posted worked for me. The whole idea of your code is to pull out the year, month and day and then recombine using the MDY function. Lots of steps compared to the two lines of code I showed.