topic Re: Excel Import in SAS Programming

Excel Import

SASPhile — Thu, 22 Feb 2018 21:26:11 GMT

i'm importing data from excel and one of the columns is formatted as shown below:

col_length
8
$200
$200
3

So when I read in using proc import the col_length in dataset is displayed as below:

Col_length
$8
$200
$200
$3

How to read the col_length as just a text so I can see as below:

Col_length
8
$200
$200
3

Re: Excel Import

ChrisNZ — Fri, 23 Feb 2018 01:03:28 GMT

Have you tried using the guessing rows option?

Do you have non-numeric values in these columns?

In any case, you can transform the columns back to numeric:

VAR1N = input(VAR1, ?? 32.10);

Re: Excel Import

SASPhile — Fri, 23 Feb 2018 01:21:32 GMT

I dont want to convert to numeric. I want them to be displayed as they are. But what happens is in excel the value are 8,$200, but after importing the sas dataset displays $8,$200.

Re: Excel Import

ChrisNZ — Fri, 23 Feb 2018 02:19:22 GMT

Maybe

format VAR comma8.0;

then?

If not, run proc contents on the table please.

Re: Excel Import

Tom — Fri, 23 Feb 2018 03:10:59 GMT

Sounds like you have a numeric column in Excel where some cells are using one display format and other cells are using a different display format. That can work in a spreadsheet since you can treat each cell as a totally independent object. But in a dataset one variable can have only one type and one display format.

So if you really want a variable that can have a value that displays as 8 for one observation and another value that displays as $200 on another then you need to make sure that the variable is a CHARACTER variable.

You can force SAS to import your column from Excel as a character variable by making at least one of the cells in the column be an actual actual character value. SAS will convert columns that have mixed numeric and character values into character variables in the SAS dataset. But I am not sure that SAS will then add the dollar signs to numeric cells that have display format that adds dollar signs. I think SAS will just convert the actual number that is stored in the cell into a string of digits. So you might need to transform your full column to contain character values.

Another way is to instead create another column in your Excel file that you can use to tell you which format you want to use to transform the value in the nuneric column. Then you could write some SAS code to use the PUTN() function to convert your numbers into the character strings that you want to see.

Re: Excel Import

SASPhile — Fri, 23 Feb 2018 15:13:55 GMT

Guessing rows doesnt work with DBMS=XLSX

PROC IMPORT OUT= WORK.input_metadata DATAFILE=
"C:\temp\reference.xlsx"
DBMS=xlsx REPLACE;
SHEET="input_Variables";
GETNAMES=YES;GUESSINGROWS=40;
RUN;
NOTE: The previous statement has been deleted.
12096 SHEET="input_Variables";
12097 GETNAMES=YES;GUESSINGROWS=40;
------------
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

Re: Excel Import

SASPhile — Fri, 23 Feb 2018 15:36:37 GMT

In excel: