BookmarkSubscribeRSS Feed
sara122
Calcite | Level 5

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.

28 REPLIES 28
Reeza
Super User

Export as Quoted CSV instead?

snoopy369
Barite | Level 11

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
Calcite | Level 5

What do I use for

put blah $ blah $ blah $; ?

snoopy369
Barite | Level 11

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

sara122
Calcite | Level 5

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

Reeza
Super User

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
Calcite | Level 5

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

art297
Opal | Level 21

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
Calcite | Level 5

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
Super User

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

sara122
Calcite | Level 5

9.3, 2010 and 64

Reeza
Super User

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
Opal | Level 21

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
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 28 replies
  • 9452 views
  • 1 like
  • 7 in conversation