topic Re: Exporting data to excel in SAS Programming

Exporting data to excel

sara122 — Fri, 25 Oct 2013 19:46:06 GMT

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.

Re: Exporting data to excel

Reeza — Fri, 25 Oct 2013 19:52:17 GMT

Export as Quoted CSV instead?

Re: Exporting data to excel

snoopy369 — Fri, 25 Oct 2013 19:55:04 GMT

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.

data _null_;

set whatever;

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

put blah $ blah $ blah $;

run;

Re: Exporting data to excel

Reeza — Fri, 25 Oct 2013 20:04:05 GMT

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.

Re: Exporting data to excel

sara122 — Fri, 25 Oct 2013 20:10:27 GMT

What do I use for

put blah $ blah $ blah $; ?

Re: Exporting data to excel

sara122 — Fri, 25 Oct 2013 20:11:35 GMT

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

Re: Exporting data to excel

art297 — Fri, 25 Oct 2013 20:31:38 GMT

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;

Re: Exporting data to excel

snoopy369 — Fri, 25 Oct 2013 20:31:55 GMT

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

Re: Exporting data to excel

sara122 — Fri, 25 Oct 2013 20:46:18 GMT

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

Re: Exporting data to excel

sara122 — Fri, 25 Oct 2013 20:48:35 GMT

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.

Re: Exporting data to excel

Reeza — Fri, 25 Oct 2013 20:53:39 GMT

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

Re: Exporting data to excel

sara122 — Fri, 25 Oct 2013 20:58:47 GMT

9.3, 2010 and 64

Re: Exporting data to excel

Reeza — Fri, 25 Oct 2013 21:28:51 GMT

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;

Re: Exporting data to excel

art297 — Fri, 25 Oct 2013 21:41:09 GMT

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.

Re: Exporting data to excel

Tom — Fri, 25 Oct 2013 22:44:43 GMT

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;

Re: Exporting data to excel

art297 — Sat, 26 Oct 2013 03:24:45 GMT

Based on your earlier comment that the output contained formatted values, when you actually need to output the raw data, I'd add a datastep before Tom's suggested code, namely:

data need;

set whatever;

format _all_;

run;

PROC EXPORT DATA= need

OUTFILE= "c:\whatever.csv"

DBMS=CSV REPLACE;

PUTNAMES=YES;

RUN;

Re: Exporting data to excel

art297 — Sat, 26 Oct 2013 15:52:00 GMT

The more I think about your problem, I still think a simple data step would provide the easiest solution. The three problems you found when I initially suggested it (namely, lrecl not being set, formated values being output, and the output not including variable names) are all easy to fix.

As such, I suggest your trying the following approach:

/*create a format to use*/

proc format;

value codes

999='Yes';

run;

/*create a test data set*/

data whatever;

retain a1-a1000 (1000*"just some test stuff, i.e., meaningless filler");

retain b1-b1000 (1000*999);

format b1-b1000 codes.;

do _n_=1 to 500;

output;

end;

run;

proc sql noprint;

select '"'||trim(name)||'"'

into :names

separated by " ','"

from dictionary.columns

where libname eq "WORK" and

memname eq "WHATEVER"

;

quit;

data _null_;

set whatever;

file "c:\whatever.csv" dlm=',' dsd lrecl=200000;

format _all_;

if _n_ eq 1 then put &names.;

put (_all_) (+0);

run;

Re: Exporting data to excel

jakarman — Sat, 26 Oct 2013 21:39:49 GMT

The most easy way to drop tables to Excel would be using a libname. The best is using examples and a paper as the SAS docs are not very clear on this topic.

An already old paper doing this well is: http://www2.sas.com/proceedings/sugi31/024-31.pdf The dino-time is even older than this.

Re: Exporting data to excel

sara122 — Mon, 28 Oct 2013 20:22:08 GMT

What is the purpose of creating a test data set? and what am I supposed to fill in for "just some test stuff, i.e., meaningless filler"? When I ran the code whatever I replaced it with became the value for all of my observations. I really appreciate all of the help!

Re: Exporting data to excel

sara122 — Mon, 28 Oct 2013 20:23:48 GMT

I tried this code and it seemed to work, except that it still output formatted values. Do you have any suggestions for resolving this?