DATA Step, Macro, Functions and more

how to use proc import to import a xlsx and make second row as column title?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

how to use proc import to import a xlsx and make second row as column title?

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


Accepted Solutions
Solution
‎10-20-2015 11:04 AM
Regular Contributor
Posts: 212

Re: how to use proc import to import a xlsx and make second row as column title?

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


All Replies
Regular Contributor
Posts: 212

Re: how to use proc import to import a xlsx and make second row as column title?

[ Edited ]

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

 

 

Occasional Contributor
Posts: 11

Re: how to use proc import to import a xlsx and make second row as column title?

Hi 

 

 

 

 

Regular Contributor
Posts: 212

Re: how to use proc import to import a xlsx and make second row as column title?

[ Edited ]

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

 

Att

Occasional Contributor
Posts: 11

Re: how to use proc import to import a xlsx and make second row as column title?

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.

Regular Contributor
Posts: 212

Re: how to use proc import to import a xlsx and make second row as column title?

 

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

 

Occasional Contributor
Posts: 11

Re: how to use proc import to import a xlsx and make second row as column title?

that's the problem because I can only import xlsx file. I wonder why SAS could not support xlsx ?
Regular Contributor
Posts: 212

Re: how to use proc import to import a xlsx and make second row as column title?

I found this option DATAROW=2

 

see if this works Smiley Happy

Occasional Contributor
Posts: 11

Re: how to use proc import to import a xlsx and make second row as column title?

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.
Solution
‎10-20-2015 11:04 AM
Regular Contributor
Posts: 212

Re: how to use proc import to import a xlsx and make second row as column title?

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

Occasional Contributor
Posts: 11

Re: how to use proc import to import a xlsx and make second row as column title?

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!
Valued Guide
Posts: 765

Re: how to use proc import to import a xlsx and make second row as column title?

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

Occasional Contributor
Posts: 11

Re: how to use proc import to import a xlsx and make second row as column title?

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
Valued Guide
Posts: 765

Re: how to use proc import to import a xlsx and make second row as column title?

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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