INPUT function error

[billi_billi]

Hello,

I am getting this error message for INPUT function, could anyone please help me with this issue? TIA.

Here is my code:

data lb;
merge adsl(in=a) lb1(in=b);
length AVALC $50. SUBJID $15.;
by USUBJID;
if a and b;
INDEX+1;
SUBJID=substr(USUBJID,13,7);
AVAL=LBSTRESN;
AVALC=LBSTRESC;
if LBCAT='COAGULATION' and LBTESTCD in ('APTT' 'INR' 'PT')
or LBCAT='URINALYSIS' and LBTESTCD in ('URBILV' 'URGLUV' 'URPHV' 'URPROTV' 'URSGV');
run;

proc sort data=lb1(where=(index(TRT01A,'Placebo')>0 and LBDTC^='')) out=lb_1(where=(input(LBDTC,e8601dt16.)<=TRTSDTM));
by INDEX;
run;

I'm getting ERROR: INPUT function reported 'ERROR: Invalid date value' while processing WHERE clause.

LBDTC is character 2023-11-07T12:50 

[PaigeMiller]

I cannot reproduce this error.

Please, for this problem and for all future problems, when there are errors in the log, show us the ENTIRE log for the PROC or DATA step that has the errors. Copying the error statement detached from the code shown in the log is rarely helpful.

Please copy the log as text and paste it into the window that appears when you click on the </> icon.

Please, when your code isn't working, provide small example data sets that we can work from. Provide the data sets as working SAS data step code (examples and instructions) and in no other format.

[ballardw]

If somehow that variable has one or more leading spaces you would get that error.

Personally I would have transformed that variable to a datetime value way before this step.

See if this removes the error message:

proc sort data=lb1(where=(index(TRT01A,'Placebo')>0 and LBDTC^=''))
 out=lb_1(where=(input( strip(LBDTC) ,e8601dt16.)<=TRTSDTM));
by INDEX;

If that doesn't generate an error then likely there are leading spaces and that should be fixed.

Why did you bother to include that code for data lb? Your problem is with the Sort of Data=lb1.

[billi_billi]

@ballardw I am still getting same error.

[ballardw]

---

@billi_billi wrote:

@ballardw I am still getting same error.

---

Create a new data set and create a new variable with the datetime value using the input function.

If you get the error then there should be more details in the log to show that should help. Then copy the log with the INVALID data lines and paste the log into a text box opened on the forum with the </> icon that appears above the message window.

data test_lb1;
   set lb1;
  dt_lbdtc=  input(LBDTC,e8601dt16.);
run;

[Patrick]

You could run below version of your Proc Sort code to investigate which source strings are causing the observed error. 

proc sort nodupkey
  data=lb1(keep=index TRT01A LBDTC TRTSDTM where=(index(TRT01A,'Placebo')>0 and LBDTC^='')) 
  out=INVESTIGATE(where=(missing(input(LBDTC,? e8601dt16.)) and not missing(LBDTC)));
  by LBDTC;
run;

Using the ? as part of the input() function will avoid the error condition.

[billi_billi]

I used this code and for one subject LBDTC does not have time and date is in this format 2023-02-22 while for all other subjects LBDTC is in this format 2023-03-27T12:10

[Tom]

Do you have values of that variable that contain just a single period?

That is what you will get if you used the PUT() function to generate the string and the MISSING option was set to a period.

323     do now=datetime(),.;
324       LBDTC=put(now,e8601dt16.);
325       now2 = input(LBDTC,e8601dt16.);
326       format LBDTC $quote. now: e8601dt16. ;
327       put (_all_) (=);
328     end;
329   run;

now=2024-05-16T00:14 LBDTC="2024-05-16T00:14" now2=2024-05-16T00:14
NOTE: Invalid argument to function INPUT at line 325 column 12.
now=. LBDTC="               ." now2=.
now=. LBDTC="               ." now2=. _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to
      missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 325:12
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

[billi_billi]

@Tom i just noticed for one of the visit one subject only have date in LBDTC (2023-02-22) while all others are date and time (2023-03-27T12:10)

[ballardw]

---

@billi_billi wrote:
@Tom i just noticed for one of the visit one subject only have date in LBDTC (2023-02-22) while all others are date and time (2023-03-27T12:10)

---

So what do you want to do?

If the variable is character you test the length and branch you code as to what to do. The question(s) you have to answer:

Is this to be considered a valid date value?

If so, since you need a date time, what time do  you want to be associated with observations like this.

[Tom · Accepted solution]

Do you care about the time of day?  If not then use DATE values instead of DATETIME values.

proc sort data=lb1(where=(index(TRT01A,'Placebo')>0 and LBDTC^='')) 
  out=lb_1(where=(input(LBDTC,yymmdd10.)<=datepart(TRTSDTM)))
;
  by INDEX;
run;

If you do then provide a default time of day.  Perhaps midnight?  You might be able to just append it to the existing value.  If the value had the full 16 bytes the appended text will be ignored because the informat specification only looks at the first 16 bytes.

proc sort data=lb1(where=(index(TRT01A,'Placebo')>0 and LBDTC^=''))
  out=lb_1(where=(input(cats(LBDTC,'T00:00'),e8601dt16.)<=TRTSDTM))
;
  by INDEX;
run;

[billi_billi]

@Tom thank you very much for your help this worked. I used midnight and append it to the value.