topic Re: Change variable from character to numeric after XLSx import in SAS Programming

Change variable from character to numeric after XLSx import

amigapomba — Tue, 18 Dec 2018 01:31:16 GMT

Hi all,

I'm imported an XLSx file into SAS University edition. For some reason three of the numeric variables have been classified by SAS as character variables, even though no observations include any characters. Can anyone tell me how to correct this?

Here is my import code:

PROC IMPORT DATAFILE="/folders/myfolders/Med.xlsx"
OUT=WORK.med2
DBMS=XLSX
REPLACE;

TIA for any help!

Re: Change variable from character to numeric after XLSx import

Reeza — Tue, 18 Dec 2018 01:58:14 GMT

Check the type in the Excel file. There isn't really a good way to control the import from an Excel file, the usual workaround suggestions are to create a CSV file instead and import that, it allows you to have full control. It's really rare to have a full column of just numbers be imported as characters though. How is missing denoted in that file?

Re: Change variable from character to numeric after XLSx import

learsaas — Tue, 18 Dec 2018 02:15:20 GMT

try ：

mixed=yes

Re: Change variable from character to numeric after XLSx import

amigapomba — Tue, 18 Dec 2018 02:20:41 GMT

Hi thanks very much for your help-

I looked closer at the entries for those variables in excel and saw that they each have one or two that were somehow entered as text. I will try to change them to numbers and repeat the import. Missing data in the Excel file are blank cells, for those three variables as well as for the seven other variables that sas correctly labeled as numeric.

Re: Change variable from character to numeric after XLSx import

amigapomba — Tue, 18 Dec 2018 03:12:02 GMT

thanks again! after i corrected those cells the import worked and all numeric variables were classified as numeric.