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 ;
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
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.
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.