DATA Step, Macro, Functions and more

Extract date from a column

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

Extract date from a column

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.

Column1Column 2
DEC16 Estimates reflect adoption12/31/2016
Mar17 Estimates reflect adoption3/31/2017
JUN15 Estimates reflect adoption6/30/2015
SEP17 Estimates reflect adoption 9/30/2017

 


Accepted Solutions
Solution
‎01-13-2018 06:52 PM
PROC Star
Posts: 1,580

Re: Extract date from a column

Posted in reply to Agent1592
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;

View solution in original post


All Replies
Solution
‎01-13-2018 06:52 PM
PROC Star
Posts: 1,580

Re: Extract date from a column

Posted in reply to Agent1592
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;
Super User
Posts: 6,632

Re: Extract date from a column

Posted in reply to novinosrin

Building upon that:

 

date=intnx('month',input(col,monyy5.),0,'e');

 

 Since the informat reads just 5 characters, the SCAN function can be eliminated.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 164 views
  • 3 likes
  • 3 in conversation