Convert char to numeric

[sg_kr]

Hi,

when i am importing data from excel sheet, the field agent code is getting as character.

when i join this with other table its throws an error of invalid datatype .

so i want to change the datatype of the dataset which i imported,

can i do it while importing itself or give me other suggestions.

here the code which i am importing.

PROC IMPORT OUT= IAR
/* changed the file name to import form XLSX*/
DATAFILE= "----------------------------------------------------------s\qry_results1.xlsx" 
DBMS=XLSX REPLACE;
/* RANGE="APPS_Total";*/
GETNAMES=YES;
/* MIXED=NO;*/
/* SCANTEXT=YES;*/
/* USEDATE=YES;*/
/* SCANTIME=YES;*/
RUN;

Also i tried to convert the variable with below steps and its not happening.

data Rtl_Rpt.IAR;
set IAR;
AGENT_CODE=input(AGENT_CODE,8..);
run;

please help

[Kurt_Bremser]

As long as you use Excel files, you are subject to the guessing of the SAS access methods for that. Excel does not have the concept of a fixed column type that is prevalent in databases.

If you want go take control over the data transfer process, abandon the Excel format. Save the data to a csv file and import that with a custom written datastep, where you specify types and other attributes.

If you need to convert variable x from character to numeric, you need to replace the variable with a new one, as you cannot change its type:

data out;
set in (rename=(x=_x));
x = input(_x,best32.);
drop _x;
run;

[sg_kr]

Thanks for your response.

i tried this earlier but this is not working

[Kurt_Bremser]

---

@sg_kr wrote:

Thanks for your response.

i tried this earlier but this is not working

---

Please post the log from that step.

[sg_kr]

3807 /*Changing char to numeric of agent_code*/
3808 data Rtl_Rpt.IAR;
3809 set IAR(rename=(AGENT_CODE=AGENT_CODE1));
3810 AGENT_CODE=input(AGENT_CODE1,BEST12.);
3811 run;

NOTE: Data file RTL_RPT.IAR.DATA is in a format that is native to another host, or the file
encoding does not match the session encoding. Cross Environment Data Access will be
used, which might require additional CPU resources and might reduce performance.
NOTE: Invalid argument to function INPUT at line 3810 column 16.
AGENT_CODE1=AGENT_CODE NAME=NAME FILING_STATUS_IND=FILING_STATUS_IND
DATE_IAR_BEGIN=DATE_IAR_BEGIN DATE_IAR_END=DATE_IAR_END YES=YES AGENT_CODE=. _ERROR_=1
_N_=3359
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 3810:16
NOTE: There were 3359 observations read from the data set WORK.IAR.
NOTE: The data set RTL_RPT.IAR has 3359 observations and 7 variables.
NOTE: Compressing data set RTL_RPT.IAR decreased size by 16.67 percent.
Compressed is 5 pages; un-compressed would require 6 pages.
NOTE: DATA statement used (Total process time):
real time 2.17 seconds
cpu time 0.03 seconds

[SuryaKiran]

If your trying this way it wont work, since AGENT_CODE is already defined as Character. You need to rename the variable before reading it.

/* This will not work */
data Rtl_Rpt.IAR;
set IAR;
AGENT_CODE=input(AGENT_CODE,8..);
run;
/* This will work*/
data Rtl_Rpt.IAR(Drop=AGENT_DUMMY);
set IAR(rename=(AGENT_CODE=AGENT_CODE_DUMMY));
AGENT_CODE=input(AGENT_CODE_DUMMY,8.);
run;

[Kurt_Bremser]

It looks like your last observation (the last row in Excel) contains another header, so you have to filter that one out:

data Rtl_Rpt.IAR;
set IAR(rename=(AGENT_CODE=AGENT_CODE1));
if AGENT_CODE1 ne 'AGENT_CODE';
AGENT_CODE=input(AGENT_CODE1,BEST12.);
run;

You can see that the error happens only once in data step iteration 3359 (_N_=3359), which corresponds to the overall number of observations in the dataset.