topic Re: Importing Excel , and proper variable names in SAS Programming

Importing Excel , and proper variable names

SASuserlot — Mon, 05 Oct 2020 22:19:28 GMT

I am trying import the excel file into sas, for some reason, I am running into issues where row I want to display as variable is not working properly. Any help greatly appreciated. I want titles as variable names and read it as data from the next row.

PROC IMPORT 
    OUT= YourNewTable
    DATAFILE= "myfolder/excelfilename.xlsx" 
    DBMS=xlsx 
    REPLACE;
    SHEET="Sheet1";
    GETNAMES=YES;
    range="Sheet1$A2:L4"
RUN;

I used above code, I am getting dataset , variable name as sponsor information then A,B,C . below is the excel sheet

Re: Importing Excel , and proper variable names

ballardw — Mon, 05 Oct 2020 22:29:06 GMT

The first row of the file provides the information that SAS uses to create variable names. Since you have two rows of "header" information the results are as you describe. Delete the entire row that contains the "Sponsor Information".

Then import.

Re: Importing Excel , and proper variable names

SASuserlot — Mon, 05 Oct 2020 22:42:34 GMT

I tried as u suggested, now I am getting the A. B, C.. in varaible I used the rang="sheet1$A2:L5". If possible can you write a code

Re: Importing Excel , and proper variable names

ballardw — Mon, 05 Oct 2020 22:52:51 GMT

@SASuserlot wrote:
I tried as u suggested, now I am getting the A. B, C.. in varaible I used the rang="sheet1$A2:L5". If possible can you write a code

remove the range or change it to read the first row.

I really don't use ranges but suppose sheet1$A1:L4 should work.

Re: Importing Excel , and proper variable names

SASuserlot — Mon, 05 Oct 2020 23:21:34 GMT

PROC IMPORT
OUT= SDSP
DATAFILE= "C:\Users\xxx\Desktop\New folder\xx\1.xlsx"
DBMS=xlsx
REPLACE;
SHEET="Sheet1";
GETNAMES=yes;
range="Sheet1$A2:0";
/* SCANTEXT=YES;*/
/* USEDATE=YES;*/
/* SCANTIME=YES;*/
RUN;

Re: Importing Excel , and proper variable names

SASKiwi — Tue, 06 Oct 2020 00:44:19 GMT

@SASuserlot - What happens if you remove the RANGE statement?