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!!??
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
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 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 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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.