BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ASU_IE
Fluorite | Level 6

Hello everyone,

 

I have a problem with the title while proc import to import a xlsx file.

 

I know how to use proc import but not sure how to make the second row as the titile. 

 

I have to use proc import and i cannot change the xlsx to xls because some pre requirements.

 

I tried startrow= and namerow=  but apprantely it does not work for me. I wonder what I could do to make the second row as the name for the columns?

 

I mean if nothing works with xlsx file, what i could do in data step to make second row as column title?

 

 

Thank you !

 

Tao

1 ACCEPTED SOLUTION

Accepted Solutions
DartRodrigo
Lapis Lazuli | Level 10

Hi, good Morning.

 

I found this link see, find anything that works for you.

 

PROC IMPORT - Microsoft Excel Workbook Files

 

But it will only represent until Excel 2007, anyway i thing this might help you.

 

Att

View solution in original post

13 REPLIES 13
DartRodrigo
Lapis Lazuli | Level 10

Hi

First create an infile to start from second row then use it in proc import

 

Try this:

  

data _null_;
infile 'C:\Users\file.tab' firstobs=2;
file exTemp1;
input;
put _infile_;
run;
PROC IMPORT DATAFILE=exTemp1
  OUT=SIM
  DBMS=TAB  REPLACE;
  GETNAMES=YES;
RUN;

Hope this helps.

 

Att

 

 

ASU_IE
Fluorite | Level 6

Hi 

 

 

 

 

DartRodrigo
Lapis Lazuli | Level 10

Yes that was a workaround to do what you need but one question, did you use the getnames  ?

 

Att

ASU_IE
Fluorite | Level 6

I do use getname= but the problem is I use SAS EG and the excel file i want to import is something look like (in proc import)

 

PROC IMPORT OUT= SASMEEXT.IVLimits_lookup DATAFILE= "\\powerteams.secondset.com\manuengg\Root Cause\AV Targets Table\Table v2.xlsx"
DBMS=xlsx REPLACE;
GETNAMES=YES;
sheet="AVLimits_lookup";
RUN;

 

 

if I use infile, Because I am using EG at my company and EG cannot find the file and gave error as

ERROR: Physical file does not exist, D:\SAS\Config\Lev1\SASApp\EXTEMP1.
ERROR: Import unsuccessful. See SAS Log for details.

DartRodrigo
Lapis Lazuli | Level 10

 

proc import file='c:\temp\op.xls' out=op dbms=xls replace;
namerow=2;
startrow=3;
getnames=yes;
run;

But this way is not suited for office2007.

If don't try this.

 

Att

 

ASU_IE
Fluorite | Level 6
that's the problem because I can only import xlsx file. I wonder why SAS could not support xlsx ?
DartRodrigo
Lapis Lazuli | Level 10

I found this option DATAROW=2

 

see if this works Smiley Happy

ASU_IE
Fluorite | Level 6
HI DartibaliRodrigo,

I don't think this will work because I check the definition of datarow = option and it will still keep the first row in excel as the title and start to read data from second row. The definition is stated as below for datarow=

DATAROW=
Context: [PROC IMPORT] DATAROW= statement

[Syntax: DATAROW=1 to 32767;]

Starts reading data from the specified row number in the delimited text file.

Default:
1 when GETNAMES=NO
2 when GETNAMES=YES (default for GETNAMES=)

Interaction:
When GETNAMES=YES, DATAROW= must be equal to or greater than 2. When GETNAMES=NO, DATAROW must be
equal to or greater than 1.
DartRodrigo
Lapis Lazuli | Level 10

Hi, good Morning.

 

I found this link see, find anything that works for you.

 

PROC IMPORT - Microsoft Excel Workbook Files

 

But it will only represent until Excel 2007, anyway i thing this might help you.

 

Att

ASU_IE
Fluorite | Level 6
Hi DartibaliRodrigo,

Thank you for the suggestion and I tried your code but my physical location is on the server side, so seems like it does not work. However, I do get an email from SAS support and actually this is a very simple problem and I just forgot the range= option. I mean I just need to specify the range start from second row. I guess the only issue for this method is you have to know how many rows in your excel file but you can always give a large number though.

Thank you again for your help!
MikeZdeb
Rhodochrosite | Level 12

Hi, try a data step ...

 

libname x 'z:\test.xls';
 
data new;
set x.'sheet1$'n ;
run;
 
libname x clear;

 

data set NEW using attached XLS with column names in row 2 ...

 

Obs     x     y     z         date    name

 1     10    20    30    15OCT2015    HEY
 2     99    99     .    01JAN1960    IT
 3      1     2     3    11NOV2011    WORKS

ASU_IE
Fluorite | Level 6
Hi Mike,

Thank you for your help! The only thing that may not apply to my case is that the file I can import has to be xlsx and I am not sure if SAS could handle this situation.

Tao
MikeZdeb
Rhodochrosite | Level 12

Hi, XLSX works too (did you  try it with an XLSX file on your own?) ...

 

libname x 'z:\test.xlsx';
 
data new;
set x.'sheet1$'n ;
run;
 
libname x clear;

 

data set NEW from XLSX file ...


Obs     x     y     z         date    name

 1     10    20    30    15OCT2015    HEY
 2     99    99     .    01JAN1960    IT
 3      1     2     3    11NOV2011    WORKS

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 14286 views
  • 1 like
  • 3 in conversation