Exporting data to excel

Reply
Occasional Contributor
Posts: 11

Exporting data to excel

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.

Super User
Posts: 17,824

Re: Exporting data to excel

Export as Quoted CSV instead?

Regular Contributor
Posts: 244

Re: Exporting data to excel

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;

Occasional Contributor
Posts: 11

Re: Exporting data to excel

What do I use for

put blah $ blah $ blah $; ?

Regular Contributor
Posts: 244

Re: Exporting data to excel

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

Occasional Contributor
Posts: 11

Re: Exporting data to excel

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

Super User
Posts: 17,824

Re: Exporting data to excel

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.

Occasional Contributor
Posts: 11

Re: Exporting data to excel

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

PROC Star
Posts: 7,363

Re: Exporting data to excel

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;

Occasional Contributor
Posts: 11

Re: Exporting data to excel

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.

Super User
Posts: 17,824

Re: Exporting data to excel

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

Occasional Contributor
Posts: 11

Re: Exporting data to excel

9.3, 2010 and 64

Super User
Posts: 17,824

Re: Exporting data to excel

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;

PROC Star
Posts: 7,363

Re: Exporting data to excel

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.

Super User
Super User
Posts: 6,500

Re: Exporting data to excel

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_) (Smiley Happy ;

RUN;

Ask a Question
Discussion stats
  • 28 replies
  • 2467 views
  • 1 like
  • 7 in conversation