IMPORT EXCEL file with minus sign in numeric value

[yiapipi]

Hi ALL,

executing a PROC IMPORT of Excel file, in the "RATING" variable, i find a dot value (.) instead of original value (2-).

the PROC IMPORT and EXPORT used is the following..

PROC IMPORT OUT= pio.DRO_478PAR_S_2
DATAFILE= "C:\Users\393571\Desktop\IRION_Schedulatore\&BMRC\Monitoraggio_DRO\ALL\DRO_478PAR-S_2.xlsx"
DBMS=EXCEL REPLACE;
RANGE="Monitoraggio_DRO_478PAR-S_2_Fla$"; 
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
run;

@@@@@@

PROC EXPORT DATA= pio.DRO_478PAR_S_1_DIR2
OUTFILE= "C:\Users\393571\Desktop\IRION_Schedulatore\&BMRC\Monitoraggio_DRO\ALL\DRO_478PAR_S_1_DIR2.xlsx"
DBMS=EXCEL REPLACE;
SHEET="NDG_ARRICCHITI_DIR";
RUN;

moreover you'll find input (DRO_478PAR-S_2_SAS) and output (DRO478PAR-S_1_DIR2_SAS) .xlsx file

Thank's for your support , ever precious

don't esitate to ask for more details or deep analysis

K Regards

Pio

[andreas_lds]

This is a common problem caused by excel not having typed columns. The value "2-" can only be stored in an alphanumeric variable, afaik, if you see "." in the dataset, then sas decided that the variable should be numeric. To permanently solve the issue drop excel as data-source and use csv instead.

[SASJedi]

Pio - I don't understand the problem. When I ran your PROC IMPORT code, the data read in just fine.

Of course, the values in Rating are character, not numeric - but that's the way they are formatted in Excel. If you just want to convert the ratings column to numeric after the import, you can do this:

data pio.Dro_478par_s_2_fixed;
   set pio.Dro_478par_s_2(rename=(rating=_r));
   rating=input(_r,TRAILSGN.);
   drop _:;
run;

[Kurt_Bremser]

As soon as you read from a text file, SAS provides a fitting informat (TRAILSGN) to read such numbers.