BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jerry898969
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User
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.

View solution in original post

3 REPLIES 3
ballardw
Super User
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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

jerry898969
Pyrite | Level 9

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1304 views
  • 0 likes
  • 3 in conversation