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
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
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
Hi DartibaliRodrig
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
Yes that was a workaround to do what you need but one question, did you use the getnames ?
Att
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.
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
I found this option DATAROW=2
see if this works
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
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.