10-25-2013 03:46 PM
I have a dataset with >2000 variables and ~550 observations. I am cleaning the data in SAS and creating SAS permanent data sets but would like to export to excel as well for people who don't use sas. Is there an easy way to do this with such a large data set? I have tried some methods posted online but none seem to work properly. Ideally I would like to export a CSV file however, there are comment fields in my data that contain commas that cause the data to get separated incorrectly in CSV files. Any help would be greatly appreciated.
10-25-2013 03:55 PM
Use DSD option when you write out your CSV, it will properly enclose your data in quotes when needed so that other properly configured software (like Excel) can read it in properly.
file "blah.csv" dlm=',' dsd;
put blah $ blah $ blah $;
10-25-2013 04:04 PM
One more thought. Consider going to Access then to Excel. This way the db structure/formats can be checked in Access and then Excel is more likely to not have import issues with the file from Access.
If its a one time thing this is a good workaround, if it continuous not as much.
10-25-2013 04:31 PM
Sara, Did you get an answer to your question about what to put in place of blah, blah?
If not, you might be able to use:
file "c:\blah.csv" dlm=',' dsd;
put (_all_) (+0);
10-25-2013 04:48 PM
This exported something, but it was not correct. There were no variable names, and there were 16,000 observations but only about 100 variables. It also exported formatted values, and not the numeric values. Is there a way to fix this? Sorry for all the questions, I have been having issues trying to get the data properly exported for a while.
10-25-2013 05:28 PM
Can you use either DDE or create an XML file using Tagsets.ExcelXP, both are fairly simple work arounds.
ods tagsets.excelxp file="C:\temp\test.xml" style=journal;
proc print data= have;
ods tagsets.excelxp close;
10-25-2013 05:41 PM
Since you need variable names, have you tried proc export? If you license sas access for pc file formats you can accomplish the export to excel directly.
10-25-2013 06:44 PM
You can use PROC EXPORT .
PROC EXPORT DATA= SASHELP.CLASS
If your data lines are too long (by default it will handle lines up to 32767 characters long) or it is too slow then you can build your own.
One quick way is to just use PROC EXPORT to write the variable names and then use your own DATA _NULL_ to write the data.
PROC EXPORT DATA= SASHELP.CLASS (OBS=0)
SET SASHELP.CLASS ;
FILE "~/temp/x.csv" MOD DSD DLM=',' LRECL=1000000 ;
PUT (_ALL_) ( ;