topic Re: Error while changing character to numberi (due to missing data points) in SAS Programming

Error while changing character to numberi (due to missing data points)

m1ny — Tue, 02 Feb 2016 12:18:23 GMT

Hello, I am trying to convert a character variable (which is number) to a numeric variable.

But an error message shows up. It won't do what I want.

Here is my log

142 data sampling3;
143 rename visitno=visit;
144 set sampling2;
145 if visit ^=' ' then visitno=input(visit,8.);
146 drop visit;
147 run;

NOTE: Invalid argument to function INPUT at line 145 column 29.
RHSP_ID=C005650 Group=1 visit=- HIVstatus=N sex=. first_hiv_confirm=. age_at_sample=. samp_date=.
vldate_coll1=. vl_copies1= vldate_coll2=. vl_copies2= vldate_coll3=. vl_copies3= vldate_coll4=.
vl_copies4= vldate_coll5=. vl_copies5= vldate_coll6=. vl_copies6= vldate_coll7=. vl_copies7=
vldate_coll8=. vl_copies8= vldate_coll9=. vl_copies9= vldate_coll10=. vl_copies10=
vldate_coll11=. vl_copies11= vldate_coll12=. vl_copies12= vldate_coll13=. vl_copies13=
vldate_coll14=. vl_copies14= vldate_coll15=. vl_copies15= visitno=. _ERROR_=1 _N_=234
NOTE: Invalid argument to function INPUT at line 145 column 29.
RHSP_ID=C005650 Group=2 visit=- HIVstatus=N sex=. first_hiv_confirm=. age_at_sample=. samp_date=.
vldate_coll1=. vl_copies1= vldate_coll2=. vl_copies2= vldate_coll3=. vl_copies3= vldate_coll4=.
vl_copies4= vldate_coll5=. vl_copies5= vldate_coll6=. vl_copies6= vldate_coll7=. vl_copies7=
vldate_coll8=. vl_copies8= vldate_coll9=. vl_copies9= vldate_coll10=. vl_copies10=
vldate_coll11=. vl_copies11= vldate_coll12=. vl_copies12= vldate_coll13=. vl_copies13=
vldate_coll14=. vl_copies14= vldate_coll15=. vl_copies15= visitno=. _ERROR_=1 _N_=235
NOTE: Invalid argument to function INPUT at line 145 column 29.
RHSP_ID=C005650 Group=3 visit=- HIVstatus=N sex=. first_hiv_confirm=. age_at_sample=. samp_date=.
vldate_coll1=. vl_copies1= vldate_coll2=. vl_copies2= vldate_coll3=. vl_copies3= vldate_coll4=.
vl_copies4= vldate_coll5=. vl_copies5= vldate_coll6=. vl_copies6= vldate_coll7=. vl_copies7=
vldate_coll8=. vl_copies8= vldate_coll9=. vl_copies9= vldate_coll10=. vl_copies10=
vldate_coll11=. vl_copies11= vldate_coll12=. vl_copies12= vldate_coll13=. vl_copies13=
vldate_coll14=. vl_copies14= vldate_coll15=. vl_copies15= visitno=. _ERROR_=1 _N_=236
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).
      3 at 145:29
NOTE: There were 1126 observations read from the data set WORK.SAMPLING2.
NOTE: The data set WORK.SAMPLING3 has 1126 observations and 38 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Re: Error while changing character to numberi (due to missing data points)

LinusH — Tue, 02 Feb 2016 12:28:41 GMT

You could suppress those messages using the ?? operator.

https://support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default/viewer.htm#p19en16vskd2vhn1vwmxpxnglxxs.htm

Re: Error while changing character to numberi (due to missing data points)

Kurt_Bremser — Tue, 02 Feb 2016 12:36:48 GMT

It indicates that in lines 234 to 236 of your input data set, visit contains a single hyphen.

So expand your condition to

if visit > ' ' and strip(visit) ne '-' then visitno = input(visit,8.);

Re: Error while changing character to numberi (due to missing data points)

m1ny — Tue, 02 Feb 2016 12:56:30 GMT

Thank you for your comments;

it was very helpful!

Re: Error while changing character to numberi (due to missing data points)

RW9 — Tue, 02 Feb 2016 13:35:05 GMT

Sorry LinusH, I wouldn't agree with supressing warnings, they come out in the log for a reason, and it would fail QC in my opinion if this type of thing is hidden.

Simplest method to get actual values is to perform the input on a compressed string - keep only numerics. Do note though the possible erroneous one last in the list, you need to think about all combinations in your data and how to handle this best, what to map to what. As this is visit data, I presume you use CDISC? If so a DB visit to SDTM visit map dataset is probably the best way to go.

data sampling3;
  input visit $;
  visitno=input(compress(visit,,"kd"),8.);
datalines;
1
2
-
a
.
a1
;
run;

Re: Error while changing character to numberi (due to missing data points)

LinusH — Tue, 02 Feb 2016 13:53:06 GMT

Agreed @RW9. If this is part of a system with regular delivery, there should definitely be a data validation step prior to the data type conversion routine. But once validated (on errors tracked separately), I see no problem of using the ?? operator.