BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

Hello

Here are 4 ways to export SAS data set into CSV  or TXT file.

Let's say that the sas data set has 250 million rows with 30 columns.

I have some questions please:

1-Which way from the 4 ways is the recommended way to do the export?

2- Do you recommend export it into CSV or TXT file?

3- About Way1- If the data set is very big then it is better not print it into the screen.

I added code -ods select none;    but then I got 0 rows in the resulted file.

What is the way to create the  file using ODS CSV but prevent print on screen?

4-About way4- what is the name of this way?

 


/*Way1----Exporting sas dataset using ods*/
ods csv file='/usr/local/SAS/SASUsers/LabRet/Adhoc/Example1.txt' options(delimiter='|');
proc print data=sashelp.cars noobs;
run;
ods csv close;


/*Way2---Exporting sas dataset using proc export into CSV file*/
proc export data=sashelp.cars
outfile='/usr/local/SAS/SASUsers/LabRet/Adhoc/Example2.csv'
dbms=csv
replace;
run;


/*Way3---Exporting sas dataset using proc export into delimited file*/
proc export data=sashelp.cars
outfile='/usr/local/SAS/SASUsers/LabRet/Adhoc/Example3.csv'
dbms=dlm
replace;
delimiter='|';
run;


/***Way4---***/
%macro export_flat_files;
%let file_extract='/usr/local/SAS/SASUsers/LabRet/Adhoc/Example4.txt';
%put &file_extract;
data _null_;
file &file_extract dsd dlm ='|';
set sashelp.cars;
put (_all_) (~);
run;
%mend;
%export_flat_files;

 

 

10 REPLIES 10
andreas_lds
PROC Star

The main difference between CSV and TXT is the default app trying to display the file. The delimiter can be the same in both file types.

I would choose the 4th way, because it has least overhead.

Ronein
Meteorite | Level 14
What do you mean " least overhead"?
Is way 4 called data set method?
Do you prefer exoprt to csv or txt( 200 million rows)
andreas_lds
PROC Star

I never had to export such a large volume, but i would use txt, to prevent the file being opened by excel accidentally.

Least overhead: the data step only does what you tell it to do. proc export has always some additional stuff, so has ods - i suspect.

Kathryn_SAS
SAS Employee

With ODS, to prevent writing to additional output destinations, use:

ods _all_ close;

Ronein
Meteorite | Level 14
Why should i have headlines? I export sas data set into csv or txt file. I dont export summary reports where headlines are recommended
ballardw
Super User

@Ronein wrote:
Why should i have headlines? I export sas data set into csv or txt file. I dont export summary reports where headlines are recommended

Some other application may need to know what each column represents. If you don't provide a header than no-one looking at the file knows what is where.

Since you are just "exporting" and not controlling the order of columns then you apparently have no concern what order any of the values appear in the resulting file.

 

Tell me what this means:

34890,16.53,AB-PDQ

No header so I doubt that you guessed the columns were 1) instrument identification, 2)hourly mean reading and 3) control setting

 

If I am told to write to a specific file structure where the order of the variables is important then the only approach I would use is a DATA Step because I never worry about the order of variables in set because it can change too easily.

Consider:

data example;
    length height 8;
   set sashelp.class;
run;

Where the first variable in SASHELP.CLASS is Name the resulting set above has Height as the first variable.

So if I EXPORT Sashelp.class and Example because they have the same values they will have data in a different order.

 

 

PS: I would never export such a large set to CSV named file because I know some idiot is going to open it in Excel and lose 200+ million rows of data and complain.

 

Reeza
Super User

@Ronein wrote:
Why should i have headlines? I export sas data set into csv or txt file. I dont export summary reports where headlines are recommended

So you don't want variable names in the data set?

 

2/3 are essentially equivalent. 

4 is data step where you specify everything so it's the most efficient as it does exactly what you tell it to and nothing else. 

 

1 has the most overhead as it's designed to do a lot more so there's more options it works through. 

 

4 is the fastest. 

2/3 are the easiest to program. 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 10 replies
  • 302 views
  • 7 likes
  • 6 in conversation