topic Re: Add DSD option to proc export in SAS Programming

Add DSD option to proc export

Ronein — Wed, 27 Sep 2023 12:45:43 GMT

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;

Re: Add DSD option to proc export

Patrick — Wed, 27 Sep 2023 12:49:30 GMT

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

Re: Add DSD option to proc export

Tom — Wed, 27 Sep 2023 12:51:53 GMT

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.

Re: Add DSD option to proc export

Ronein — Wed, 27 Sep 2023 12:52:26 GMT

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

Re: Add DSD option to proc export

Tom — Wed, 27 Sep 2023 12:57:14 GMT

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

Re: Add DSD option to proc export

Ronein — Wed, 27 Sep 2023 12:57:58 GMT

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.

Re: Add DSD option to proc export

Tom — Wed, 27 Sep 2023 13:01:00 GMT

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

Re: Add DSD option to proc export

ChrisHemedinger — Wed, 27 Sep 2023 13:01:27 GMT

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

Re: Add DSD option to proc export

Ronein — Wed, 27 Sep 2023 13:10:30 GMT

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)

Re: Add DSD option to proc export

Ronein — Wed, 27 Sep 2023 13:13:36 GMT

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

Re: Add DSD option to proc export

Tom — Wed, 27 Sep 2023 13:27:50 GMT

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

Re: Add DSD option to proc export

Ronein — Wed, 27 Sep 2023 14:03:20 GMT

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

Re: Add DSD option to proc export

Ronein — Wed, 27 Sep 2023 14:09:32 GMT

I dont need extra quotes but I afraid that without it the TXT(or CSV file) will not cut the columns successfully. What do you think?

Re: Add DSD option to proc export

Ronein — Wed, 27 Sep 2023 14:11:15 GMT

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='.';

Re: Add DSD option to proc export

ballardw — Wed, 27 Sep 2023 14:55:52 GMT

What does "cut the columns successfully" actually mean?

If you are worried about the position, i.e. creating fixed column data, then Export is not what you should use.

@Ronein wrote:
I dont need extra quotes but I afraid that without it the TXT(or CSV file) will not cut the columns successfully. What do you think?

Re: Add DSD option to proc export

Tom — Wed, 27 Sep 2023 16:05:37 GMT

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.

Re: Add DSD option to proc export

Kurt_Bremser — Wed, 27 Sep 2023 16:52:37 GMT

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

Re: Add DSD option to proc export

Ronein — Wed, 27 Sep 2023 17:13:09 GMT

So you recommend export to .csv file and not .txt file? Why?

Re: Add DSD option to proc export

Tom — Wed, 27 Sep 2023 17:14:55 GMT

@Ronein wrote:
I dont need extra quotes but I afraid that without it the TXT(or CSV file) will not cut the columns successfully. What do you think?

To know whether or not the file can be properly read by some particular tool we need to know what tool you are using. Most tools can read CSV files properly. Most can also read similar files made with an other delimiter (in fact in a lot of countries that use comma as the decimal point indicate they routinely use semicolon as the delimiter instead of comma in "CSV" files).

I dislike using TAB as the delimiter in files because it makes the file hard to look at. And also if you open the file with a text editor it might replace the tabs with spaces to align the text to the next traditional tab stop and make the file unreadable.

If your data has a lot of commas (perhaps it has long descriptive text strings or using COMMA or DOLLAR format on some largish numbers) then I would use | as the delimiter.

Use a different extension on the file's name so that it does not accidentally get opened by whatever tool your machine is set to default to when it sees a CSV file. The name of the file has NOTHING to do with the content. It is just a suggestion to help the user make a quick judgement of what it MIGHT be.

proc export data=have file='myfile.txt' dbms=csv ;
  delimiter='|';
run;

Re: Add DSD option to proc export

Ronein — Wed, 27 Sep 2023 17:33:02 GMT

I am a bit confused. You export to csv file ( dbms=csv)but used delimiter that is not comma?? As i understood csv file has comma delimiter by definition but here you export to csv file with other delimiter??