topic Re: Export big sas data set into csv or txt in SAS Programming

Export big sas data set into csv or txt

Ronein — Thu, 14 Sep 2023 10:49:54 GMT

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;

Re: Export big sas data set into csv or txt

andreas_lds — Thu, 14 Sep 2023 11:12:24 GMT

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.

Re: Export big sas data set into csv or txt

Ronein — Thu, 14 Sep 2023 12:16:19 GMT

What do you mean " least overhead"?
Is way 4 called data set method?
Do you prefer exoprt to csv or txt( 200 million rows)

Re: Export big sas data set into csv or txt

andreas_lds — Thu, 14 Sep 2023 12:43:22 GMT

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.

Re: Export big sas data set into csv or txt

Kathryn_SAS — Thu, 14 Sep 2023 12:57:06 GMT

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

ods _all_ close;

Re: Export big sas data set into csv or txt

Kurt_Bremser — Thu, 14 Sep 2023 13:46:12 GMT

ODS is the worst way, it performs like wading through molasses.

If you use #4, you must add code to write the headerline, if such is required.

Re: Export big sas data set into csv or txt

Ronein — Thu, 14 Sep 2023 14:25:44 GMT

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

Re: Export big sas data set into csv or txt

ballardw — Thu, 14 Sep 2023 14:42:53 GMT

@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.

Re: Export big sas data set into csv or txt

Kurt_Bremser — Thu, 14 Sep 2023 15:41:09 GMT

Please learn to read.

Quote from my post:

If you use #4, you must add code to write the headerline, if such is required.

Re: Export big sas data set into csv or txt

Kurt_Bremser — Thu, 14 Sep 2023 15:49:28 GMT

And your methods 1 to 3 will create headers.

Re: Export big sas data set into csv or txt

Reeza — Thu, 14 Sep 2023 16:02:31 GMT

@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.