BookmarkSubscribeRSS Feed
Florent
Quartz | Level 8
Hello,

I'd like to know if this is possible to create a .CSV file using the "||" as delimiter with the DSD option ?


My test dataset contains:

field1 field2 field3 field4
2 123 432 0.0101
2 456 . 0.01
2 . 876 0.0356
2 98 654 0.0112

And I'm currently using the following code to generate the .CSV file:

%macro MACEXPORT(PART=);
data _null_;

set WORK.∂

file "C:\Documents and Settings\ECF525\Desktop\&part..csv" delimiter='||' DSD DROPOVER lrecl=32767;

put field1 @;
put field2 @;
put field3 @;
put field4 ;

run;
%MEND MACEXPORT;

Unfortunately, I just got the following content in my .CSV file ("|" instead of "||" as delimiters):
2|123|432|0.0101
2|456||0.01
2||876|0.0356
2|98|654|0.0112


Thank you in advance for your time,
Florent
11 REPLIES 11
advoss
Quartz | Level 8
See SAS online documentation:
Restriction: Even though a character string or character variable is accepted, only the first character of the string or variable is used as the output delimiter. This differs from INFILE DELIMITER= processing.
Tim_SAS
Barite | Level 11
Well, the doc says "Even though a character string or character variable is accepted, only the first character of the string or variable is used as the output delimiter." However, there's no reason you can't make up your own CSV output. Would this (or some variation of this) work?

[pre]
data;
input field1 field2 field3 field4;
datalines;
2 123 432 0.0101
2 456 . 0.01
2 . 876 0.0356
2 98 654 0.0112
;;;;
data _null_;
set;
file log dsd;
put field1 (field2--field4) (+(-1) "||");
run;
[/pre]
Florent
Quartz | Level 8
Thank you for your answers.

@Tim: It works great !

Could you please just explain me the meaning of this part ?
(+(-1) "||)

Thanks again,
Florent
Tim_SAS
Barite | Level 11
The whole statement is a formatted PUT in the form
[pre]
put (variable-list) (format-list);
[/pre]
Within (format-list) you can put formats, character strings, and pointer controls. You can read about it in the Put Statement, Formatted section of the SAS Language Reference: Dictionary online: http://support.sas.com/onlinedoc/913/docMainpage.jsp. See especially the example "Writing a Character between Formatted Values".

The DSD option puts commas between the values by default. The +(-1) pointer control is an trick to prevent that by backing up the column pointer 1 column. I think I must've learned about it on sas-l or something.
Florent
Quartz | Level 8
Thank you !
deleted_user
Not applicable
if it was just to provide delimiters, the DSD option would not be neccessary here.
DSD provides only a single character delimiter on lines generated with put statements, so extra arrangements are needed to create multiple character delimiters like "||".
Hence the solution Tim@ has offered.
I want to recommend one small change.
I would still recommend DSD here, because it also provides protection against delimiters embedded in data values. Imagine a pipe mark embedded in a character string ... if that is the reason for proposing double pipes as delimiter, beware it is equally unsafe in 100% of cases (if one pipe mark can occur, one day two will happen, probably)
DSD protects against embedded delimiters by "quoting the value". However it looks for the delimiter defined or defaulted on the FILE statement.
So that is the change I recommend to the solution offered by Tim@SAS
[pre] FILE LOG DSD DLM='|' ; [/pre]
Then the protection routines of the FILE statement will be protecting against pipe marks rather than the default delimiter which is a comma.

Before SAS8 introduced DSD handling on the file statement, the +(-1) technique was effective at removing the default blank provided between variables listed in the (variable-list) when there are no formats in the (format-list). That was the technique for generating csv type output before SAS8: [pre] data _null_;
set sashelp.class ;
put (_all_)( +(-1) ',' ) ;
run; [/pre]
Of course the old technique is still effective, just unneccessary, except when a multi-character delimiter is needed.

sorry about the "history lesson", but remember always protect against delimiters embedded in data.

PeterC
Florent
Quartz | Level 8
There is no chance for us to find a pipe in the variables' values and we mainly used the DSD option for the printing of the missing values (double occurence of the delimiter in that case).

Even more, if I add the dlm option, it displays a quotation mark before and after the pipe marks:

2"||"123"||"432"||"0.0101
2"||"456"||""||"0.01
2"||""||"876"||"0.0356
2"||"98"||"654"||"0.0112



By the way, thanks for this reminder.
deleted_user
Not applicable
your experience is interesting.
Here is the log demonstrating no extra quote marks for me![pre] 3 data _null_;
4 set sashelp.class(obs=3) ;
5 file log dsd dlm='|' ;
6 put (_all_)( +(-1) '!!' ) ;
7 run;

!!Alfred!!M!!14!!69!!112.5
!!Alice!!F!!13!!56.5!!84
!!Barbara!!F!!13!!65.3!!98
NOTE: There were 3 observations read from the data set SASHELP.CLASS.[/pre]

Is that not what you wanted ?

apologies..... I'm more used to bangs than pipes...
but the revision stil does not introduce those quotes [pre] 3 data _null_;
4 set sashelp.class(obs=3) ;
5 file log dsd dlm='|' ;
6 put (_all_)( +(-1) '||' ) ;
7 run;

||Alfred||M||14||69||112.5
||Alice||F||13||56.5||84
||Barbara||F||13||65.3||98
NOTE: There were 3 observations read from the data set SASHELP.CLASS.[/pre] from bang! to pipe|


Message was edited by: Peter_c
Florent
Quartz | Level 8
I still have the same result 😕
The code that I executed is:

32 %macro MACEXPORT(PART=);
33 data _null_;
34
35 set WORK.∂
36 file "C:\Documents and Settings\ECF525\Desktop\&part..csv" dlm='|' DSD DROPOVER
36 ! lrecl=32767;
37
38 put field1 (champ2 c3 f4) ( +(-1) '||' );
39 run;
40 %MEND MACEXPORT;
41
42 %macexport(part=test);

NOTE: The file "C:\Documents and Settings\ECF525\Desktop\test.csv" is:
File Name=C:\Documents and Settings\ECF525\Desktop\test.csv,
RECFM=V,LRECL=32767

NOTE: 4 records were written to the file "C:\Documents and Settings\ECF525\Desktop\test.csv".
The minimum record length was 20.
The maximum record length was 25.
NOTE: There were 4 observations read from the data set WORK.TEST.
NOTE: DATA statement used:
real time 0.07 seconds
cpu time 0.01 seconds


The expected result is:
2||123||432||0.0101
2||456||||0.01
2||||876||0.0356
2||98||654||0.0112

but instead of it I get the following result:
2"||"123"||"432"||"0.0101
2"||"456"||""||"0.01
2"||""||"876"||"0.0356
2"||"98"||"654"||"0.0112

I really don't understand why we have different results with the same query ...
What if you specifies the variables' names as me (instead of using _all_) ?
deleted_user
Not applicable
I guess our platforms create the difference.
Here, I use SAS9.1.3 sp4 on z/OS

The implementation of the method requires slightly different file handling on z/OS, but a couple of SAS language features allow the code to reduce to something entirely platform independent. The demo code below even loads a version of your data

do you see anything inappropriate in this non-macro version of your code
[pre]data part ;
infile cards dsd dlm='|' ;
input field1 champ2 c3 f4 ;
list ; cards ;
2|123|432|0.0101
2|456||0.01
2||876|0.0356
2|98|654|0.0112
;
filename csvf temp ;
data _null_ ;
file csvf dsd dlm='|' ;
set part ;
put field1 (champ2 c3 f4)( +(-1) '||' ) ;
run;

data _null_;
infile csvf ;
file log ;
input;
put _infile_ ;
run; [/pre]
On my platform z/OS, the log of that last datastep shows this copy of the csv file][pre] NOTE: The infile CSVF is:
Dsname=SYS08056.T083949.RA000.TMPE164Z.R0189747,
Unit=3390,Volume=PI3540,Disp=NEW,Blksize=27920,
Lrecl=80,Recfm=FB

2||123||432||0.0101
2||456||||0.01
2||||876||0.0356
2||98||654||0.0112
NOTE: 4 records were read from the infile CSVF.
NOTE: The DATA statement used 0.01 CPU seconds and 14223K.[/pre]

Are you able to execute the demo above and report the results?

PeterC
Florent
Quartz | Level 8
Indeed that's because of the different platforms.

Here they use the following one:
NOTE: SAS (r) Proprietary Software Release 8.2 (TS2M0)

But I executed the program on a remote session where the platform is:
NOTE: SAS (r) Proprietary Software Release 9.1 (TS1M3)

I have the same results than you on the 9.1 platform but still different results on the 8.2 platform.

Florent

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
  • 11 replies
  • 1692 views
  • 0 likes
  • 4 in conversation