DATA Step, Macro, Functions and more

How read a date variable containing letters

Reply
Super Contributor
Posts: 272

How read a date variable containing letters

Dear,

 

I have a variable with date contains UN and UNK string. How to read it.  I need the output in character date form. Please help

 

  id                  date1                                 date2

  1                   06/01/2012

  2                     .                                      UN-UNK-2014

 3                                                             UN-Sep-2014

 

 

output needed;

id                                chadate

1                                 2012-06-01  

2                                 2014

3                                 2010-09   

Super User
Posts: 17,912

Re: How read a date variable containing letters

How are 2/3 being determined?

Respected Advisor
Posts: 4,659

Re: How read a date variable containing letters

[ Edited ]

Tedious, but simple

 

data have;
input id    (date1 date2) (:$12.);
datalines;
  1     06/01/2012    .
  2       .           UN-UNK-2014
 3        .           UN-Sep-2014
 ;

data want;
set have;
length chadate $10 y $4 m $3 d $2;
date = coalescec(date1, date2);
y = scan(date,3,"-/");
m = upcase(scan(date,2,"-/"));
d = scan(date,1,"-/");
if anyalpha(m) then
    m = choosec(whichc(m,"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC") + 1,
        " ","01","02","03","04","05","06","07","08","09","10","11","12");
if anyalpha(d) or missing(m) then d = " ";
chadate = catx("-", y, m, d);
drop date y m d;
run;

proc print; run;


PG
Super User
Posts: 9,687

Re: How read a date variable containing letters

As long as you make sure there are only UN and UNK.



data have;
input id    (date1 date2) (:$12.);
datalines;
  1     06/01/2012    .
  2       .           UN-UNK-2014
 3        .           UN-Sep-2014
 ;
run;

options missing=' ';
data want;
 set have;
 if find(date2,'UNK') and find(date2,'UN') then do;
  temp_date=scan(date2,-1,'-'); missing='M';
 end;
 else if find(date2,'UN') then do;
  temp_date=put(input(cats('01',substr(date2,3)),date11.),yymmd8.); missing='D';
 end;
 date=coalescec(put(input(date1,mmddyy10.),yymmdd10.),temp_date);
 drop temp_date;
run;

Super Contributor
Posts: 272

Re: How read a date variable containing letters

Thank you very much for help. I created the following code. It produced the output I needed. But I want to know is there any mistake in it. Please provide your thoughts

 

if date1 ^= . then chardate1=put(date1,is8601da.);
if compress(date2,'-') = : 'UNUNK' then chardate2= put(input(substr(compress(date2,'-'),6),best.),10.);
else if compress(date2,'-') = : 'UN' then chardate3= put(input(substr(compress(date2,'-'),3),monyy7.),yymmd7.);
if chardate1 ^= '' then chardate=chardate1;
else if chardate2 ^= '' then chardate=chardate2;
else if chardate3 ^= '' then chardate=chardate3;

Super User
Posts: 9,687

Re: How read a date variable containing letters

Look Good.
Ask a Question
Discussion stats
  • 5 replies
  • 293 views
  • 0 likes
  • 4 in conversation