I have a simple have table. Column 1 is a string variable.
Column1 |
DEC16 Estimates reflect adoption |
Mar17 Estimates reflect adoption |
JUN15 Estimates reflect adoption |
SEP17 Estimates reflect adoption |
I would like a want table where column 2 is a date in a DATE9. format. We assume that it is the end of the month.
Could you please help.
Column1 | Column 2 |
DEC16 Estimates reflect adoption | 12/31/2016 |
Mar17 Estimates reflect adoption | 3/31/2017 |
JUN15 Estimates reflect adoption | 6/30/2015 |
SEP17 Estimates reflect adoption | 9/30/2017 |
data have;
input col $50.;
datalines;
DEC16 Estimates reflect adoption
Mar17 Estimates reflect adoption
JUN15 Estimates reflect adoption
SEP17 Estimates reflect adoption
;
data want;
set have;
date=intnx('month',input(scan(col,1),monyy5.),0,'e');
format date mmddyy10.;
run;
data have;
input col $50.;
datalines;
DEC16 Estimates reflect adoption
Mar17 Estimates reflect adoption
JUN15 Estimates reflect adoption
SEP17 Estimates reflect adoption
;
data want;
set have;
date=intnx('month',input(scan(col,1),monyy5.),0,'e');
format date mmddyy10.;
run;
Building upon that:
date=intnx('month',input(col,monyy5.),0,'e');
Since the informat reads just 5 characters, the SCAN function can be eliminated.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.