Partial date conversion

[sasuser123123]

Similar to this old thread: https://communities.sas.com/t5/SAS-Procedures/Partial-date-conversion/td-p/197941

I've a date like 21:unk:1997
01:03:1998
10:09:1994
Unk:09:1999
Unk:unk:2000
I want replace day 'unk'=01 if month is in 01-06
Else unk=30
Month 'unk'='01' if day is in 01-15
Else month='12';
Anybody knows the process please let me know!!??

[Tom]

So is the first delimited value supposed to be the day of the month and the second value the month number?

If so then once you have replaced the text with digits you can use the DDMMYY informat to convert it to a real date 

[Tom]

You didn't give a rule for missing day and missing month.

data have;
  input string $20.;
cards;
21:unk:1997
01:03:1998
10:09:1994
Unk:09:1999
Unk:unk:2000
;

data want ;
  set have;
  day=input(scan(string,1,':'),??4.);
  month=input(scan(string,2,':'),??4.);
  year=input(scan(string,3,':'),??4.);
  if missing(day) then do;
    if month in (1:6) then day=1;
    else if month in (7:12) then day=30;
  end;
  if missing(month) then do;
    if day in (1:15) then month=1;
    else if day in (16:31) then month=12;
  end;
  if n(day,month,year)=3 then date=mdy(month,day,year);
  format date yymmdd10.;
run;
proc print;
run;

Obs       string       day    month    year          date

 1     21:unk:1997      21      12     1997    1997-12-21
 2     01:03:1998        1       3     1998    1998-03-01
 3     10:09:1994       10       9     1994    1994-09-10
 4     Unk:09:1999      30       9     1999    1999-09-30
 5     Unk:unk:2000      .       .     2000             .

[sasuser123123]

Tnqq so much..and I've a doubt.so why you were used n(day,month,year)=3

[Tom]

---

@sasuser123123 wrote:
Tnqq so much..and I've a doubt.so why you were used n(day,month,year)=3

---

To avoid NOTE in log about MDY() function generating missing values because of missing inputs.

[sasuser123123]

And If we have both day and month =unk then the values are 01 and 01

[Tom]

---

@sasuser123123 wrote:
And If we have both day and month =unk then the values are 01 and 01

---

You should be able to make the needed changes to test for that condition and assign those values.

[sasuser123123]

By doing that above program..I got a note on log window that shows..
Note:: Invalid Numeric data,month='unk'
Note:: Invalid Numeric data ,day='unk'

[sasuser123123]

I've used if day='unk' then day=1

[Tom]

Is DAY a numeric variable or a character variable in your program? If it is numeric then it cannot have a value like 'unk'. If it is character then you shouldn't try to assign a numeric value to it.
Perhaps you meant:
if missing(day) then day=1;

[sasuser123123 · Accepted solution]

I've dates like01:unk:1997
18:unk:1999
Unk:09:2000
Unk:unk:2010;
So I'm trying to convert these partial dates to normal by using these conditions...
If date is missing so take 01 when month between jan-jul otherwise take 30
If month is missing take 01 when date between 1 t0 15 otherwise 12..
So I run below program it shows some notes like invalid numeric data month='unk' ......
So please modify below program
Data hx;
Set for;
Day=scan(start date,1,':');
Month=scan(start date,2,':');
Year=scan(start date,3,':');
If day='unk' then do;
If month in(01:06) then day='01';
Else if month in (07:12) then day='30';
End;
If month='unk' then do;
If day in(01:06) then month='01';
Else if day in (07:12) then month='12';
End;
If month='unk' then month='01';
If day='unk' then day='01';
Date=mdy(month,day,year);
Run;

[sasuser123123]

Can get Your Contact..

[sasuser123123]

I've dates like01:unk:1997
18:unk:1999
Unk:09:2000
Unk:unk:2010;
So I'm trying to convert these partial dates to normal by using these conditions...
If date is missing so take 01 when month between jan-jul otherwise take 30
If month is missing take 01 when date between 1 t0 15 otherwise 12..
So I run below program it shows some notes like invalid numeric data month='unk' ......
So please modify below program
Data hx;
Set for;
Day=scan(start date,1,':');
Month=scan(start date,2,':');
Year=scan(start date,3,':');
If day='unk' then do;
If month in(01:06) then day='01';
Else if month in (07:12) then day='30';
End;
If month='unk' then do;
If day in(01:06) then month='01';
Else if day in (07:12) then month='12';
End;
If month='unk' then month='01';
If day='unk' then day='01';
Date=mdy(month,day,year);
Run

[Tom]

If you do not define your variable explicitly with a LENGTH or ATTRIB statement then SAS will define them based on how you first use them in the code.  So DAY is defined as CHARACTER by this statement because it is assigning it the result of a character function.

day=scan(start date,1,':');

If you define DAY as a character variable then if you compare it to numeric values, as you are doing in this test:

If day in(01:06)

then SAS will at run time try to convert the characters in DAY into a number.  (Note: The syntax N:M is short hand for all of the integers between N and M, inclusive.)

That is why it is giving you the warning about unk being an invalid number.

Go back the version I posted where DAY, MONTH and YEAR are created as numeric variables.  And modify your tests for UNK to test for missing values instead.  Such as:

if day=. then do;

or 

if missing(day) then do;

[sasuser123123]

Thank you much...For your support