DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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

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)Smiley SadColumn).
      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


Accepted Solutions
Solution
‎02-02-2016 07:55 AM
Super User
Posts: 5,260

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


All Replies
Solution
‎02-02-2016 07:55 AM
Super User
Posts: 5,260

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

You could suppress those messages using the ?? operator.

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

Data never sleeps
Occasional Contributor
Posts: 7

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

Thank you for your comments;

it was very helpful!

 

Super User
Posts: 6,972

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

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.);
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,430

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

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;
 
Super User
Posts: 5,260

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

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.

Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 259 views
  • 4 likes
  • 4 in conversation