topic Re: Convert best12 to date in SAS Programming

Convert best12 to date

merveayibogan — Wed, 31 Jan 2024 11:55:26 GMT

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

Re: Convert best12 to date

PaigeMiller — Wed, 31 Jan 2024 13:44:02 GMT

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

Re: Convert best12 to date

merveayibogan — Wed, 31 Jan 2024 17:43:45 GMT

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;

Re: Convert best12 to date

Tom — Wed, 31 Jan 2024 18:23:05 GMT

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;

Re: Convert best12 to date

PaigeMiller — Wed, 31 Jan 2024 19:13:43 GMT

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