BookmarkSubscribeRSS Feed
dht115
Calcite | Level 5

Hello, 

 

I have some data and need to compare with the other variable. 

If my data has full date (day month and year), I need to compare with day, month, and year. 

If my data has partial date (month and year only), I need to compare only month and year. 

If my data has partial date (year only), I need to compare only year. 

 

I create new_date variable in below code which is working as expected. I need to compare new_date with date2 variable as describe above. 

 

When I use for smaller dataset, it is working. When I am using with bigger dataset, I am getting a warning message for Month and Year date. 

NOTE: Invalid argument to function INPUT at line 87 column 7.

 

 

data have;
input date1 $11. date2 $10.;
datalines;
UN UNK 2020 01MAY2020
UN UNK 2019 15MAY2018
UN JAN 2019 08JAN2020
01 JAN 2017 24JUN2018
7 SEP 2021 11DEC2021
UN UNK 0000 12DEC2021
;
run;


data want;
set have;

new_date = compress(prxchange('s/^UN|^00|^UK|^UNK|0000 //i',-1,date1));

*full date;
if length(new_date)>=8 then do;
 base=input(strip(NEW_date),date9.);
 compare=input(strip(substr(date2,1)),date9.);
format base compare date9.;
end;
*missing day (month and year);
if length(NEW_date)=7 then do;
 base=input(strip(NEW_date),monyy7.);
 compare=input(strip(substr(date2,3)),monyy7.);
format base compare monyy7.;
end;
*missing day and month (year);
if length(NEW_date)=4 then do;
 base=input(strip(NEW_date),best.);
 compare=input(strip(substr(date2,6)),best.);
format base compare best.;
end;

flag=(base<=compare);
*if flag = 1 then output;
run;

 

3 REPLIES 3
PaigeMiller
Diamond | Level 26

When I use for smaller dataset, it is working. When I am using with bigger dataset, I am getting a warning message for Month and Year date.

NOTE: Invalid argument to function INPUT at line 87 column 7.

 

Please show us the LOG (all of it for this particular data step, every single line for this data step) as we don't know what line 87 is. (In fact, from now on, whenever you get something unexpected in the log, show us the entire log for that DATA step of for that PROC)

 

Also, this topic has been discussed a zillion times here in the SAS Communities, a search for "partial dates" will turn up more ideas than you can actually use. Here is a simple solution: https://communities.sas.com/t5/SAS-Programming/How-to-convert-partial-character-dates-to-numeric/m-p...

--
Paige Miller
Kurt_Bremser
Super User

When you have an invalid argument NOTE, you also get a listing of all variable values at that particular moment written to the log. This will provide you with a clue.

And NEVER (as in NEVER) post just a single message from the log. Post the whole step code and all messages. If "invalid data" messages repeat with a similar pattern, one will suffice, but everything else can be (and usually is) important.

Kurt_Bremser
Super User

When you know that a column has to be a date in every observation, read it as such. For partial dates, it is better to read day, month and year separately (makes the following code simpler).

data have;
input day :$2. month :$3. year :$4. date2 :date9.;
format date2 yymmdd10.;
datalines;
UN UNK 2020 01MAY2020
UN UNK 2019 15MAY2018
UN JAN 2019 08JAN2020
01 JAN 2017 24JUN2018
7 SEP 2021 11DEC2021
UN UNK 0000 12DEC2021
;

data want;
set have;
if day = "UN"
then do;
  if month = "UNK"
  then check = (input(year,4.) = year(date2));
  else if year ne "0000"
  then check = (input("01" !! month !! year,date9.) = intnx('month',date2,0,'b'));
end;
else check = (input(day !! month !! year,date9.) = date2);
run;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2095 views
  • 0 likes
  • 3 in conversation