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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.