Stuck Trying to Convert String Date (M/DD/YYYY) into Date

[davidvalentine]

Hello Everyone,

I'm scratching my head at this problem.  Here's what I'm trying to do:

1. Use cats to create a concatenation between a calculated Month, a delimiter, an arbitrary day (15 in this case), and a calculated two digit year.  I then use the input function on this to force it to MMDDYY10. format, but I keep getting 21564, not 01/15/2019 as I intended.

2. The string date is shown next to the formulaically derived date.

proc sql;
create table example2 as 
select * from
	(select
		put(&YYMM.,Best4.) as YYMM,
		Acct_No,
		sum(Balance) as BalAmt,
		avg(Balance) as Avg_Bal_Amt,
		avg(Mon_Var) as Month,
		cats(calculated Month,"/",15,"/",substr(calculated YYMM,1,2)) as Date,
		input(cats(calculated Month,"/",15,"/",substr(calculated YYMM,1,2)),MMDDYY10.) as Date2
from example1
);
run;
quit;

The output is shown like so:

Date (str)         Date2 (Num)

1/15/19           21564

1/15/19           21564

1/15/19           21564

1/15/19           21564

Mon_Var is simply the month number such as 1, 2, 3, etc (an integer).  YYMM is a macro that is predefined.  In this case, it is 1901 (a string).  I am, in effect, trying to take an arbitrary day like 15, and create a date out of this YYMM variable, but to no avail.  Any help is appreciated!

-Valentine

[novinosrin · Accepted solution]

Do you wanna format the Date for display?

like

input(cats(calculated Month,"/",15,"/",substr(calculated YYMM,1,2)),MMDDYY10.) as Date2 format=mmddyy10.

[davidvalentine]

THAT'S IT!!!!!! 

I've spent about an hour trying to figure it out... and you responded in less than a minute with an answer.  novinosrin, you are a life saver; thank you!!!

[novinosrin]

You are not alone. We all have had those moments like having my cell phone in my pocket and drove 30 miles to a  prev location looking for it and eventually found it in my pocket hahaha

[Tom]

It is doing what you asked it to do. Since you did not attach any format to the new variable DATE2 it is just showing you the raw number of days since 1960. 21,564 is the value for the 15th date of January in the year 2019.

1119  data _null_;
1120    date='15JAN2019'd ;
1121    put date= comma10.;
1122  run;

date=21,564

Tell SAS how you want the number of days displayed by attaching a format.

proc sql;
create table example2 as 
  select put(&YYMM.,Z4.) as YYMM
       , Acct_No
       , sum(Balance) as BalAmt
       , avg(Balance) as Avg_Bal_Amt
       , avg(Mon_Var) as Month
       , catx('/',calculated Month,15,substr(calculated YYMM,1,2)) as Date
       , input(catx('/',calculated Month,15,substr(calculated YYMM,1,2)),mmddyy10.)
         as Date2 format=date9.
  from example1
;
quit;

PS DO NOT USE ONLY TWO DIGITS FOR YEAR!!!!