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

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

ASU_IE — Fri, 16 Oct 2015 20:02:49 GMT

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

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

DartRodrigo — Fri, 16 Oct 2015 20:32:48 GMT

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

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

ASU_IE — Fri, 16 Oct 2015 20:34:01 GMT

Hi DartibaliRodrigo,

thank you for your help! I did saw this from some other posts but I wonder if I could use Proc import to make this happen?

Thanks,

Tao

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

DartRodrigo — Fri, 16 Oct 2015 20:40:26 GMT

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

Att

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

ASU_IE — Fri, 16 Oct 2015 20:48:42 GMT

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.

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

DartRodrigo — Fri, 16 Oct 2015 20:49:46 GMT

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

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

DartRodrigo — Fri, 16 Oct 2015 20:55:19 GMT

I found this option DATAROW=2

see if this works

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

ASU_IE — Fri, 16 Oct 2015 20:58:12 GMT

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

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

ASU_IE — Sat, 17 Oct 2015 04:30:42 GMT

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.

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

DartRodrigo — Mon, 19 Oct 2015 11:26:04 GMT

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

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

MikeZdeb — Mon, 19 Oct 2015 16:25:56 GMT

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

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

ASU_IE — Mon, 19 Oct 2015 18:02:25 GMT

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

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

MikeZdeb — Mon, 19 Oct 2015 18:46:54 GMT

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

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

ASU_IE — Tue, 20 Oct 2015 15:04:05 GMT

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!