Numeric variable to date9 / Use of weekday function

[Nici]

Hello everyone,

I'm sorry to post on such an "easy" topic but somehow adjusting dates in SAS never works for me. I've red through so many posts on this topic and tried the solutions shown but they never seam to work for me, I don't know why. Shouldn't it be easy to adjust the data, it feels so hard in SAS? 😄

So basically, I have a numeric variable (format Best 12. informat 12.) 'anncmt' which I want to convert to Date9. I have to use Date9. since otherwise the weekday(anncmt) function does not work properly [which is confusing to me as well :D].

data have;
input pcusip date permno ret cap anncmt code shrout prc;
datalines;
1 19990202 a 0.01 400 19990209 3 100 4
1 19990202 b 0.04 100 19990209 3 100 1
1 19990203 a 0.01 400 19990209 3 100 4
1 19990203 b 0.04 100 19990209 3 100 1
2 20000101 c 0.01 800 20000310 3 400 2
2 20000102 c 0.02 800 20000310 3 400 2
3 19300812 d 0.03 750 19300810 3 750 1
3 19300813 d 0.03 750 19300810 3 750 1
;

What I'm trying right now is:

data want;
set have;

anncmt2 = input(put(anncmt, Date9.), BEST12.);
drop anncmt;
rename anncmt2 = anncmt;

run;

It would be nice to finally have an easy go to way to format numeric variables to date variables.

Thank you for your help

BR

Nici

[PeterClemmensen]

I think this is what you want 🙂

data have;
input pcusip date permno $ ret cap anncmt code shrout prc;
datalines;
1 19990202 a 0.01 400 19990209 3 100 4
1 19990202 b 0.04 100 19990209 3 100 1
1 19990203 a 0.01 400 19990209 3 100 4
1 19990203 b 0.04 100 19990209 3 100 1
2 20000101 c 0.01 800 20000310 3 400 2
2 20000102 c 0.02 800 20000310 3 400 2
3 19300812 d 0.03 750 19300810 3 750 1
3 19300813 d 0.03 750 19300810 3 750 1
;


data want;
   set have;

   anncmt2 =input(put(anncmt, 8.), yymmdd8.);

   format numanncmt date9.;
   drop anncmt;
run;

proc print data=want;
run;

[Nici]

I changed it up a little bit but then it worked for me, thank you! 🙂

[ballardw · Accepted solution]

Almost:

data want;
   set have;
   anncmt = input(put(anncmt,8.),yymmdd8.);
   format anncmt date9.;
run;

With input you want to use a format that looks somewhat the existing values to turn into a date. So since you have data that looks like YYYYMMDD the informat would be yymmdd, the 8 indicates how many characters are expected and implies a 4-digit year (month and day are never more than 2 digits). The PUT bit uses the 8. format to create a string like '19990209' which is what the yymmdd8 format expects to read.

Not need to go about the bit of creating a new variable and dropping/renaming since the variable you want is numeric and the source variable was numeric.

You actually do not need to have the date9., or any date format, actually applied for the date functions such as weekday to work but the value needs to be as expected and the date format will allow use humans to evaluate some of that more easily in some cases.

[Nici]

Thank you very much for your reply and your detailed explanation (always nice for a newbie like me).

BR

Nici

[SaraBoltman1]

This always works for me - basically turn it into text, substring it into the month, day and year and then create it as a date. Sounds complicated, but it's only 3 lines.

data want;
format textdate $8. anncmt date9.;
set have (rename=(anncmt=old_anncmt));
textdate=old_anncmt;
anncmt=mdy(substr(textdate,5,2),substr(textdate,7,2), substr(textdate,1,4));

run;

you might want to drop textdate from your output but I've left it in so you can see what's going on. BY the way, I noticed your permno variable isn't coming in properly, SAS thinks it's a number. You just need to put a $ in there to make it text.

Sara

[andreas_lds]

If possible, change the importing step and  use format- and informat-statements while reading the data:

data have;
    length pcusip 8 date 8 permno $ 1 ret cap anncmt code shrout prc 8;
    informat date anncmt yymmdd.;
    format date anncmt date9.;
    
    input pcusip date permno ret cap anncmt code shrout prc;
    datalines;
1 19990202 a 0.01 400 19990209 3 100 4
1 19990202 b 0.04 100 19990209 3 100 1
1 19990203 a 0.01 400 19990209 3 100 4
1 19990203 b 0.04 100 19990209 3 100 1
2 20000101 c 0.01 800 20000310 3 400 2
2 20000102 c 0.02 800 20000310 3 400 2
3 19300812 d 0.03 750 19300810 3 750 1
3 19300813 d 0.03 750 19300810 3 750 1
;
run;

[Tom]

You have it backwards.

Let's look at your statement.

anncmt2 = input(put(anncmt, Date9.), BEST12.);

This says to treat the number 19,990,209 as the number of days since 1960 and generate a string that is in the format ddMONyyyy. Note that 19 million days since 1960 would be in the year 56,690.  SAS cannot process dates that large.  Plus even if it could a string like 01JAN56690 could not be interpreted by SAS as a number since it would contain letters for the month of the year.  (PS The concept of a "best" informat doesn't make sense.  SAS will just use the normal 12. informat if you ask for the best12. informat.)

You can use the YYMMDD informat to convert your digits into a date, but first you need to convert the number into a string.  Informats always convert strings to values.  Formats convert values to strings.

anncmt2 = input(put(anncmt, 8.), yymmdd8.);