Exporting data to excel

[sara122]

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.

[Reeza]

Export as Quoted CSV instead?

[snoopy369]

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.

ie

data _null_;

set whatever;

file "blah.csv" dlm=',' dsd;

put blah $ blah $ blah $;

run;

[sara122]

What do I use for

put blah $ blah $ blah $; ?

[snoopy369]

Your variables.  Look up a basic SAS manual/tutorial for file output techniques, it'll cover this in it.

[sara122]

I can't list all my variables ,there are more than 2000, that would be a huge set of  code.

[Reeza]

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.

[sara122]

It is something I have to do on a weekly basis so I don't think this will work

[art297]

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:

data _null_;

  set whatever;

  file "c:\blah.csv" dlm=',' dsd;

  put (_all_) (+0);

run;

[sara122]

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.

[Reeza]

What version of SAS are you on and what version of Excel? And what bits 32/64?

[sara122]

9.3, 2010 and 64

[Reeza]

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;

run;

ods tagsets.excelxp close;

[art297]

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.

[Tom]

You can use PROC EXPORT .

PROC EXPORT DATA= SASHELP.CLASS

            OUTFILE= "~/temp/x.csv"

            DBMS=CSV REPLACE;

     PUTNAMES=YES;

RUN;

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)

            OUTFILE= "~/temp/x.csv"

            DBMS=CSV REPLACE;

     PUTNAMES=YES;

RUN;

DATA _NULL_;

   SET SASHELP.CLASS ;

   FILE  "~/temp/x.csv" MOD DSD DLM=',' LRECL=1000000 ;

   PUT (_ALL_) (:) ;

RUN;