Add DSD option to proc export

[Ronein]

Hello

I want to export sas data set into txt file.

I want to use proc export and not data set method.

I want to add double quotation to the values  using DSD option. 

My question- What is the way to add DSD option to proc export?

data Have;
Set sashelp.class;
if sex='F' then age=.;
label
Name='Client_Name'
Sex='Gender'
Age='Age'
Height='Height'
Weight='Weight'
;
run;

proc export data=Have
outfile="/usr/local/SAS/SASUsers/LabRet/Adhoc/creditCards/RRR.txt"
dbms=tab; 
run;

[Patrick]

The quotes will get added if you create a .csv file using dbms=csv. The delimiter will then of course be a comma.

[Ronein]

But I want TXT file and not CSV (Since file will have 250 million rows)

[Tom]

---

@Ronein wrote:
But I want TXT file and not CSV (Since file will have 250 million rows)

---

That statement does not make any sense.  A CSV file is a TEXT file.  What do you think a TXT file is that would be different from a CSV file?  Do you mean it uses a different delimiter?  Use the DELIMITER= statement of PROC EXPORT to change the delimiter used.

[ChrisHemedinger]

Check out the %DS2CSV macro for some flexible options on creating text data from a data set.

[Ronein]

I prefer TXT file and not CSV because I dont want users confuse and open the file in EXCEL (since it is very big file).

How would  you export  a very big sas data set into TXT?

Please take into consideration that there are chart columns that contain special characters  such as : comma,space,tab,single quote,double quotes and so  on...

[Tom]

---

@Ronein wrote:

I prefer TXT file and not CSV because I dont want users confuse and open the file in EXCEL (since it is very big file).

How would  you export  a very big sas data set into TXT?

Please take into consideration that there are chart columns that contain special characters  such as : comma,space,tab,single quote,double quotes and so  on...

---

So you are talking about the NAME of the file you created, not how the content is arranged.  You can use any name you want for the file.  If you don't want the user (or more importantly the user's operating system) to think it is a file that should be opened with EXCEL then do not use an extension on the filename that is one that your operating system has associated with EXCEL.

The only special characters you need to worry about are embedded end of line markers.  SAS cannot process files with end of line markers embedded in one the values, whether or not the value is quoted.  Excel and other tools can read such a file when the value with the end of line marker is quoted, but normal SAS tools do not quote such variables.

Personally what I do is remove or replace any LF ('0A'x) or CR ('0D'x) characters that might be in the file before writing it to a delimited file.

[Ronein]

Thanks, Just that things be clear, 

1-Do you recommend me to  create .CSV file and not  .TXT file ?

Can you explain shortly why?

2-Can you show the PROC EXPORT code that I should use in order to create the CSV file properly?

I know the basic code, what supplements should I add to this code to struggle case of  commas in char column values?

options missing='';/**Convert numeric missing valu into null  value***/
proc export data=sashelp.class
outfile="path\class.csv"
dbms=csv 
replace;
run;
options missing='.';

I will appreciate if you may show full code.

Cheers

[Tom]

PROC EXPORT already adds the DSD option when it creates the data step to write the file.

If you are not seeing any extra quotes in the output file it is because the values do not contain either tabs or quotes so the extra quotes are not needed in the output file. 

The extra quotes are only needed in a delimited text file to make it so the file can be properly parsed. If the value does not have a delimiter there is no need to add the quotes.  You have to also add quotes when the value contains a quote to prevent quotes in the values from looking quotes inserted to protect embedded delimiters.

[Ronein]

When I used data step method to export then there were double quotes in all values even if the values dont contain delimiter  value......

so-

When using proc export you say that automatically DSD option is applied BUT only added double quotes to values with delimiter value.

When using data step to export with DSD option then for any value there is double quotes.

[Tom]

---

@Ronein wrote:

When I used data step method to export then there were double quotes in all values even if the values dont contain delimiter  value......

so-

When using proc export you say that automatically DSD option is applied BUT only added double quotes to values with delimiter value.

When using data step to export with DSD option then for any value there is double quotes.

---

Show the code you used that does that as it is NOT the way a normal data step would behave.

1295  data _null_;
1296    set sashelp.class(obs=3);
1297    file log dsd;
1298    put (_all_) (+0);
1299  run;

Alfred,M,14,69,112.5
Alice,F,13,56.5,84
Barbara,F,13,65.3,98

Do you really need the extra quotes added?  I would think with so many observations you would not want to make the text file larger than it needs to be.

[Ronein]

So what do you advice to do?

there may have char columns that contain special symbols like double quotes,single quotes,comma,space and so on.....

I prefer TXT and not CSV because I dont want that users confuse and open it in EXCEL (Since this file is so big)

[Ronein]

data Have(drop=Name rename=(_Name=Name));
Set sashelp.class;
if sex='F' then age=.;
IF Name='Henry' then _Name='Hen ry';else _Name=Name;
run;


/***********WAY1*****************/
/***********WAY1*****************/
/***********WAY1*****************/
options missing='';/**Convert numeric missing valu into null  value***/
proc export data=Have
outfile="/usr/local/SAS/SASUsers/LabRet/Adhoc/creditCards/WAY1.txt"
dbms=tab; 
run;
options missing='.';


/***********WAY2*****************/
/***********WAY2*****************/
/***********WAY2*****************/
options missing='';/**Convert numeric missing valu into null  value***/
%let file_extract=/usr/local/SAS/SASUsers/LabRet/Adhoc/creditCards/Way2.txt;
%put &file_extract;
data _null_;
file "&file_extract." dsd dlm="09"x;
set Have;
put (_all_) (~);
run;
options missing='.';

[Tom]

You are getting the extra quotes because you use the ~ modifier in your PUT statement.  Remove that.  You do need to have something inside the ( ) for the format list though.  I like to use +0 there as it indicate to move the curser by zero places, in otherwords to do nothing special.

put ( _all_ ) ( +0 );

You do not need to worry about the character used for displaying missing values since with the DSD option there nothing at all written for a missing value.

[Kurt_Bremser]

---

@Ronein wrote:

So what do you advice to do?

there may have char columns that contain special symbols like double quotes,single quotes,comma,space and so on.....

I prefer TXT and not CSV because I dont want that users confuse and open it in EXCEL (Since this file is so big)

---

Simply add in your documentation of the file that it must not be opened with Excel, only with software which is capable to work with such data.