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
Do you wanna format the Date for display?
like
input(cats(calculated Month,"/",15,"/",substr(calculated YYMM,1,2)),MMDDYY10.) as Date2 format=mmddyy10.
Do you wanna format the Date for display?
like
input(cats(calculated Month,"/",15,"/",substr(calculated YYMM,1,2)),MMDDYY10.) as Date2 format=mmddyy10.
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!!!
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
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!!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.