topic Re: IMPORT EXCEL file with minus sign in numeric value in SAS Programming

IMPORT EXCEL file with minus sign in numeric value

yiapipi — Wed, 28 Oct 2020 10:19:40 GMT

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

Re: IMPORT EXCEL file with minus sign in numeric value

andreas_lds — Wed, 28 Oct 2020 11:40:57 GMT

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.

Re: IMPORT EXCEL file with minus sign in numeric value

SASJedi — Wed, 28 Oct 2020 12:28:01 GMT

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;

Re: IMPORT EXCEL file with minus sign in numeric value

Kurt_Bremser — Wed, 28 Oct 2020 12:18:45 GMT

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