topic Re: Partial date conversion in SAS Programming

Re: Partial date conversion

sasuser123123 — Fri, 19 Jul 2019 16:56:30 GMT

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!!??

Re: Partial date conversion

Tom — Fri, 19 Jul 2019 16:58:13 GMT

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

Re: Partial date conversion

Tom — Fri, 19 Jul 2019 17:05:16 GMT

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             .

Re: Partial date conversion

sasuser123123 — Fri, 19 Jul 2019 17:12:30 GMT

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

Re: Partial date conversion

sasuser123123 — Fri, 19 Jul 2019 17:13:23 GMT

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

Re: Partial date conversion

Tom — Fri, 19 Jul 2019 18:54:12 GMT

@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.

Re: Partial date conversion

Tom — Fri, 19 Jul 2019 18:55:24 GMT

@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.

Re: Partial date conversion

sasuser123123 — Sat, 20 Jul 2019 05:36:34 GMT

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'

Re: Partial date conversion

sasuser123123 — Sat, 20 Jul 2019 05:42:11 GMT

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

Re: Partial date conversion

Tom — Sat, 20 Jul 2019 06:52:37 GMT

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;

Re: Partial date conversion

sasuser123123 — Sat, 20 Jul 2019 08:32:02 GMT

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;

Re: Partial date conversion

sasuser123123 — Sat, 20 Jul 2019 09:40:32 GMT

Can get Your Contact..

Re: Partial date conversion

sasuser123123 — Sat, 20 Jul 2019 10:17:03 GMT

Re: Partial date conversion

Tom — Sat, 20 Jul 2019 15:28:29 GMT

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;

if missing(day) then do;

Re: Partial date conversion

sasuser123123 — Sat, 20 Jul 2019 15:35:13 GMT

Thank you much...For your support