what's wrong with this code & how to impute date with end date of month if date part not a valid.

[Moksha]

 
proc format;
value $month
'JAN' = 'JAN'
'FEB' = 'FEB'
'MAR' = 'MAR'
'APR' = 'APR'
'MAY' = 'APR'
'JUN' = 'JUN'
'JUL' = 'JUL'
'AUG' = 'AUG'
'SEP' = 'SEP'
'OCT' = 'OCT'
'NOV' = 'NOV'
'DEC' = 'DEC'
other = ' '
;
RUN;

data raw_datedata;
input date $10.;
date_datepart = substr(date,1,2);
date_monthpart=put(upcase(substr(date,3,3)),$month.);
date_yearpart=substr(date,6);

put date_datepart= date_monthpart= date_yearpart=;
cards;
12jan2012
ukfeb2022
13dec2023
ukunk2012
15aug1995
ukjan2016
ukunk2019
;

run;

proc print;run;

data dt_impute;
set raw_datedata;

if anydigit(date_datepart) = 0 then date_datepart = '';
else date_datepart = date_datepart;
if date_datepart eq '' and date_monthpart eq '' then date_datepart='01' and date_monthpart = 'DEC';
run;

proc print;run;
Output of the above code:
 
 

Obs date date_datepart date_monthpart date_yearpart
1 12jan2012 12 JAN 2012
2 ukfeb2022 FEB 2022
3 13dec2023 13 DEC 2023
4 ukunk2012 0 2012
5 15aug1995 15 AUG 1995
6 ukjan2016 JAN 2016
7 ukunk2019 0 2019


Can anyone please let me know why for record 4 and 7, why date_datepart and date_monthpart has value 0 and ' ' instead of '01' and 'DEC'?
 
Also, please let me know how to replace with the end of the month date in case of date is not a valid one.
 
Thanks
 

[PGStats · Accepted solution]

Incorrect syntax... AND is a logical operator it cannot be used to mean Do this AND that

Replace

if date_datepart eq '' and date_monthpart eq '' then date_datepart='01' and date_monthpart = 'DEC';

with

if date_datepart eq '' and date_monthpart eq '' then do;
date_datepart='01';
date_monthpart = 'DEC';
end;

[Moksha]

Thank you for the reply. I have used the code as per your suggestion, it worked. 

[ballardw]

What specific result date do you want to impute when you only have a YEAR in the value?

When you only have a month and year?

Do you have any cases with only day and year values? Like 15unk2023? If so, what month would you want to impute?

Here is how I would go about creating a SAS date value from that stuff. Using TRANWRD and Strip to remove the UK and UNK bits from the date text value.

data raw_datedata;
input date $10.;
date=strip(tranwrd(date,'uk',''));
date=strip(tranwrd(date,'unk',''));
sasdate  = input(date,?? date9. );
if missing(sasdate) then do;
  /*sas informat monyy will impute the first day of the month*/
  if length(date)=7 then sasdate=input(date,monyy7.);
  /* impute month as 6, day of month as 1 replace as desired*/
  else if length(date)=4 then sasdate=mdy(6,1,input(date,4.));
end;
format sasdate date9.;
cards;
12jan2012
ukfeb2022
13dec2023
ukunk2012
15aug1995
ukjan2016
ukunk2019
;

[Moksha]

Thank you for the reply. The imputation rules are:

If date part is not valid then replace it with last day of the month in the given date.  For example, ukfeb2022 has invalid date part 'uk' and month is feb and year 2022 is not a leap year I should get it as 28FEB2022.

If both date part and month part are not valid then it should be 31DEC for example xyunk2019 has invalid date part xy and invalid month part unk and hence it should be 31DEC2019.

For the first part, I have used the following and it is working:

imputed_date = put(intnx('mon',input(cats('01',date_monthpart,date_yearpart),date9.),0,'e'),$date9.);

[ballardw]

---

@Moksha wrote:

Thank you for the reply. The imputation rules are:

If date part is not valid then replace it with last day of the month in the given date.  For example, ukfeb2022 has invalid date part 'uk' and month is feb and year 2022 is not a leap year I should get it as 28FEB2022.

If both date part and month part are not valid then it should be 31DEC for example xyunk2019 has invalid date part xy and invalid month part unk and hence it should be 31DEC2019.

For the first part, I have used the following and it is working:

imputed_date = put(intnx('mon',input(cats('01',date_monthpart,date_yearpart),date9.),0,'e'),$date9.);

---

Strongly suspect the line above throws an invalid format message with the $date9. format. You have, created a date value and $date, a character format, would not be applied by SAS.

Using INTNX with my code for the end of the month and Dec 31 for the year only values:

data raw_datedata;
input date $10.;
date=strip(tranwrd(date,'uk',''));
date=strip(tranwrd(date,'unk',''));
sasdate  = input(date,?? date9. );
if missing(sasdate) then do;
  /*sas informat monyy will impute the first day of the month, use INTNX 
    to increment to the end of the mont*/
  if length(date)=7 then sasdate=intnx('month',input(date,monyy7.),0,'E');
  /* impute month as 6, day of month as 1 replace as desired*/
  else if length(date)=4 then sasdate=mdy(12,31,input(date,4.));
end;
format sasdate date9.;
cards;
12jan2012
ukfeb2022
13dec2023
ukunk2012
15aug1995
ukjan2016
ukunk2019
;

[Moksha]

You are absolutely right that the code that I have mentioned has given the following warning message:

WARNING: Character format specified for the result of a numeric expression.

Thank you very much for pointing out and providing the code. However, I want the sasdate to be character but in the code provided by you it's numeric. Can you please advise how to achieve this? 

I have tried using put function but since sasdate is numeric, getting warning messages for this also.

Once, again thank you very much for your valuable guidance.

[Moksha]

By removing $, the following worked : imputed_date = put(intnx('mon',input(cats('01',date_monthpart,date_yearpart),date9.),0,'e'),date9.); If there are any better ways to do this, please advise.

[ballardw]

---

@Moksha wrote:

You are absolutely right that the code that I have mentioned has given the following warning message:

WARNING: Character format specified for the result of a numeric expression.

Thank you very much for pointing out and providing the code. However, I want the sasdate to be character but in the code provided by you it's numeric. Can you please advise how to achieve this? 

I have tried using put function but since sasdate is numeric, getting warning messages for this also.

Once, again thank you very much for your valuable guidance.

---

PUT the sasdate variable with the format you want.

Since you haven't described how this is actually used other than some other program will somehow use the file you still may not need that step as how you create the file could be where the format is actually needed.

[Moksha]

Sure and once again thank you very much.