variable names disappearing after exporting to Excel

[ballardw · Accepted solution]

---

@Reeza wrote:
Rather than a PROC EXPORT try a data step export where you're manually specifying the LRECL for one - I suspect you're running into lines that are larger than 32K characters most likely. I think you can get around that in a data step. I'm not sure why you can't build your conversion into #5 from a SAS data set rather than a re-import.....but that's a design issue.

---

It is starting with Proc Import for the same file format repeatedly, though apparently it changed.

Time to let @Cassie-Andra know that Proc Import writes a data step to the log when importing CSV.

So copy the Data step from the log and paste into the editor. Make the changes there for the proper variable type by changing informats (or Attrib statements).

Then you can read a different file by changing the infile statement and create a different output data set by changing the name.

When changes are made you can add new informat information for the new variables and modify the INPUT statement.

And since I saw the word "survey" you might investigate whether the Survey software will output files in SAS or SPSS files. I know of several packages that provide those options. Which can save you a ton of headaches.

[Cassie-Andra]

@ballardw ,

I once tried using an infile statement (a long while ago), and I can't recall at all why I abandoned that strategy and moved to proc import and proc export.  Your suggestion circled me back to trying it and it worked!!!  I used Excel's formulas and auto-fill to rename all the variables (they originally come in as a specific pattern of letters and numbers) and specify the lengths and formats I wanted, and then copied/pasted them over the appropriate sections in the import code.  That did the trick!!  No more guessing on which options I should choose in my statements. 

Unfortunately, this particular survey platform does NOT offer SAS output files although eventually we will be migrating to a platform that does due to issues like this. 

Thank you for the reminder and giving me the inspiration to revise my process, which actually turned out to be a lot simpler than my original method. 

[mkeintz]

---

@Cassie-Andra wrote:

... stuff deleted ...

When we only had about 3500 variables, my current process was as follows:

1 - download the csv from the survey website

2 - import it into SAS

3 - rename variables and create a few new ones

4 - output into csv again

5 - re-import back into SAS with an infile statement forcing the formats of all these variables.  

The advantage to doing it this way is that if a survey respondent doesn't fill out some information in a cell, for ex: a date of birth, I can then force that cell to be a date format instead of a blank character.  I need this because if I set these variables together and if their formats aren't the same, I get issues.  

Your explanation appears to suggest that steps 4 and 5 are intended to do no more the change formats (or informats) of some variables.  If so, replace those steps with a single PROC DATASETS, which would allow you to change formats (and labels) in place.  No need to read/write the data - it just re-writes the header information in the data set.

[data_null__]

Use a FILENAME with LRECL option for example.

filename xout 'test.csv' lrecl=100000;
proc export file=xout data=sashelp.shoes dbms=csv  replace;
   run;