topic Stuck Trying to Convert String Date (M/DD/YYYY) into Date in SAS Programming

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

davidvalentine — Thu, 09 Jan 2020 19:28:29 GMT

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

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

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

novinosrin — Thu, 09 Jan 2020 19:30:19 GMT

Do you wanna format the Date for display?

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

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

davidvalentine — Thu, 09 Jan 2020 19:32:48 GMT

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!!!

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

novinosrin — Thu, 09 Jan 2020 19:34:25 GMT

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

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

Tom — Thu, 09 Jan 2020 19:42:12 GMT

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!!!!