topic Importing excel with over 11,000 columns causes varname to change in SAS Procedures

Importing excel with over 11,000 columns causes varname to change

jerry898969 — Tue, 15 Sep 2015 18:53:41 GMT

I have a large xlsx file with no headings just data. I'm expecting sas to import the file and name each variable after what column it is in.

For example the first variable is name "A" and so on till "ZZ". After "ZZ" I get var703 and it continues incrementing from there till it gets to var11664.

How can I keep it using the column letters or make all the variables have var and the column number?

This is the sas code I'm using

proc import 
		datafile="c:\temp\temp.xlsx"
		out=temp
		dbms=xlsx
		replace ;
getnames=no ;		
guessingrows=2147483647 ;
run ;

Thank you

Re: Importing excel with over 11,000 columns causes varname to change

ballardw — Tue, 15 Sep 2015 21:24:13 GMT

None of the proc import options I am aware of let you control column names if they are not in the dataset.
A separate step using proc datasets would likely be your best bet. Unless you insert a row in the xlsx and assign the column header there before import.

Re: Importing excel with over 11,000 columns causes varname to change

RW9 — Wed, 16 Sep 2015 09:34:31 GMT

May I ask why you would want to? As always, I would suggest that you create a datastep import, use the code generate by proc import, and I would change all character variables, e.g. A, AA, to varX version. That way you can relate to them in arrays and such like. I would also ask what kind of actual processing you are likely to do on 11,000 variables? That seems to be a very large amount, Personally I wouldn't like to work with dataset of more than 100 variables, and that itself is a challenge.

With this data, I would write a small VBA macro pseudo code of:

open csv file

loop over all columns

for each row in column write out column_name,column_value

end loop

This would provide you with a thin csv file with two columns, one with the column name, one with the result. Makes your import program so much easier, you can process the name as you like, and if you really have to work with the transposed data you can transpose up by column_name.

Re: Importing excel with over 11,000 columns causes varname to change

jerry898969 — Wed, 16 Sep 2015 14:20:20 GMT

Thank you RW9 and Ballardw for your responses.

We have a very large data processes that has changed and we were forced to take a different route. We wanted the import to have a systematic naming convention so in our other programs we could loop through the variables. What I'm working on now is just creating my own naming for each column and importing it with getnames=yes.

I know that is a very large amount of columns. We used to use a different application but they have changed it and now instead of getting multiple tables they are giving us just one wide one. It will be split up into many smaller tables.

Thank you again both of you for your help.