PROC IMPORT Question on file with over 600 columns

[katie215]

Hi-

I'm trying to work with a very large excel worksheet (it's got just over 600 variables/columns). When I import it as an excel sheet it gets cut-off (I now know the limit is 255). However when I converted the .xlsx file to CSV and then imported that I am able to get all of the variables to come into one SAS data set with no problem, however part way through the data set the variables were renamed with VAR213, VAR214, etc through the end.

Is there a way to get SAS to either import all of the excel sheet, or to keep all of the variable names in the import of the CSV file? Any help is greatly appreciated!!

Thanks,

Katie

[ballardw]

The most likely cause of the VAR213 type naming is either no column heading or very long column headings that match that of another column. SAS needs to have a unique handle to assign the variable name but if you have multiple columns labeled TOTAL, for example, then there is a problem as there can be only one variable named Total in a SAS data set.

You have a couple of choices. One is you can change the names of the variables using RENAME with Proc datasets.

If you are going to read multiple files in this format then it is worth modifying the datastep code generated by proc import to read the CSV file. You can copy the code from the log into the editor and modify the variable names on the informat, format and input statements.

[Reeza]

@BallardW suggestion of obtaining the proc import code and manually fixing is a good one.

If this is a one time process another suggestion would be import all variables as Var1-Var600 and then rename them.

Since your data is in Excel you could copy the first row to a new sheet, transposing it to obtain a list. Then use some concatenate functions to create the text required.