CODEWITHSUNDEEP
sas
Buras
Buras

Reputation: 3099

How to import an excel to sas with getnames = no?

I want to specify new names so I use getnames=no property:

data mylib.test; 
   infile  "C:\Users\test.xlsx" ;
   input var1   $ Opened_Date mmddyy8.  salary dollar9.2;
   DBMS=EXCEL ;
   range="Sheet5$";
   getnames=no;
   mixed=no;
   scantext=yes;
   usedate=yes;
   scantime=yes;
   datarow=3;
run;

But this does not import anything

PS the following code with getnames=yes works fine . This means that there is no problems with excel file . But i don't want to use yes I need getnames=no

PROC IMPORT OUT= WORK.TEST 
            DATAFILE= "C:\Userstest.xlsx" 
            DBMS=EXCEL REPLACE;
     RANGE="Sheet5$"; 
     GETNAMES=YES;
     MIXED=NO;
     SCANTEXT=YES;
     USEDATE=YES;
     SCANTIME=YES;
RUN;

Upvotes: 1

Views: 5180

Answers (1)

Joe
Joe

Reputation: 63424

PROC IMPORT OUT= WORK.TEST 
            DATAFILE= "C:\Userstest.xlsx" 
            DBMS=EXCEL REPLACE;
     RANGE="Sheet5$"; 
     GETNAMES=NO;
     MIXED=NO;
     SCANTEXT=YES;
     USEDATE=YES;
     SCANTIME=YES;
RUN;

The data step is not helpful in this case. You can't import an excel file that way (practically speaking).

After this, you then create a data step and can rename things however you want from the generic names initially assigned by the PROC IMPORT.

An alternative that looks like the data step method is libname access.

libname myexcel excel "c:\Usertest.xlsx" getnames=no scantext=yes mixed=no usedate=yes scantime=yes;

Then you can access the file like

data test;
set myexcel,'Sheet5$'n;
rename f1=var1 f2=opened_date (...more...);
run;

I tend to use PROC IMPORT as it's a bit easier to understand for others, but both are equivalent in how they work (PROC IMPORT creates this libname for you, basically).

Upvotes: 2

Related Questions