Your code appears to be assuming that PROC IMPORT will name the variable COL1, COL2 , ...
Instead it will name the variable A,B,...
So if you have enough columns in the XLSX sheet there will already be a variable named I.
Are you saying you only want to scan the values of the first ROW in the speadsheet to determine the number of COLUMNS in the spreadsheet to skip?
To get the names and values of the first observation in a dataset you can use PROC TRANSPOSE with the OBS=1 dataset option. Make sure to use VAR _ALL_ so that transpose takes all of the variables and not just the numeric ones.
Let's do an example. First let's make an XLSX file. Then read it in with GETNAMES=NO. Then transpose to get the list of variable names and first row values.
filename xlsx temp;
proc export data=sashelp.class file=xlsx dbms=xlsx replace;
run;
proc import file=xlsx dbms=xlsx out=want replace;
getnames=no;
run;
proc transpose data=want(obs=1) out=names;
var _all_;
run;
proc print;
run;
Results:
Obs _NAME_ _LABEL_ COL1
1 A A Name
2 B B Sex
3 C C Age
4 D D Height
5 E E Weight
So you might do something like this:
* Generate fileref that points to the XLSX file ;
filename xlsx "&folder/&file";
* Generate dataset with list of new names to use ;
data newnames;
input newname $32. ;
cards;
Job_No
Requesting_Client
Contact_Full_Name
Start_time
End_Time
Language
;
* read in the file without names ;
proc import file=xlsx dbms=xlsx out=want replace;
getnames=no;
run;
* Get the header row ;
proc transpose data=want(obs=1) out=names;
var _all_;
run;
* Find the first column location and assign newnames ;
* Set RANGE value for PROC IMPORT ;
%let range=;
data renames ;
colnum + 1;
set names ;
if lowcase(col1)='job no.' then do;
call symputx('range',cats('$',_name_,'2:'));
firstvar = colnum;
end;
retain firstvar ;
if firstvar then set newnames;
run;
* Generate old=new rename pairs ;
proc sql noprint;
select catx('=',_name_,newname) into :renames separated by ' '
from renames
where firstvar and newname ne ' '
;
quit;
* Read the file starting from second row and found column without name ;
* Rename the variables ;
proc import file=xlsx dbms=xlsx out=want(rename=(&renames)) replace;
range="&range";
getnames=no;
run;
... View more
