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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: