Text File with Text Qualifier " and Field Delimiter |

[vrs_431]

How to create a text file from sas dataset in the following format.

"ABC"|"BCD"|"CVF"|"CVV"|

Text Qualifier should be " and Field Delimiter should be |

[data_null__ · Accepted solution]

259  data _null_;

260     file log dsd dlm='|';

261     set sashelp.class;

262     put (_all_)(~);

263     run;

"Alfred"|"M"|"14"|"69"|"112.5"

"Alice"|"F"|"13"|"56.5"|"84"

"Barbara"|"F"|"13"|"65.3"|"98"

"Carol"|"F"|"14"|"62.8"|"102.5"

"Henry"|"M"|"14"|"63.5"|"102.5"

"James"|"M"|"12"|"57.3"|"83"

"Jane"|"F"|"12"|"59.8"|"84.5"

"Janet"|"F"|"15"|"62.5"|"112.5"

"Jeffrey"|"M"|"13"|"62.5"|"84"

"John"|"M"|"12"|"59"|"99.5"

"Joyce"|"F"|"11"|"51.3"|"50.5"

"Judy"|"F"|"14"|"64.3"|"90"

"Louise"|"F"|"12"|"56.3"|"77"

"Mary"|"F"|"15"|"66.5"|"112"

"Philip"|"M"|"16"|"72"|"150"

"Robert"|"M"|"12"|"64.8"|"128"

"Ronald"|"M"|"15"|"67"|"133"

"Thomas"|"M"|"11"|"57.5"|"85"

"William"|"M"|"15"|"66.5"|"112"

[vrs_431]

Hi Data_Null_,

Thanks for your reply. it is working. But I am struck with other problem.

For the same file if the field is empty it is writing in text file as |" "| . i.e. space in between " ". But the other system(Sibel) does

not allowing this. So my question is can we write text file such that it does not keep space for the blank value??

Ex:

"Alfred"|""|""|"69"|"112.5"

Output as above. Please let me know if I am not clear.

Many Thanks

[Ksharp]

Here are two approaches.

data class;

set sashelp.class;

if _n_ in (1 4 ) then call missing(sex);

run;

ods csv file='c:\x.csv' options(delimiter='|') ;

proc print data=class;run;

ods csv close;

data _null_;

infile 'c:\x.csv' ;

file 'c:\want.csv' ;

input;

_infile_=compress(_infile_,' ');

put _infile_;

run;

Ksharp

[vrs_431]

Hi Ksharp,

Thanks for the reply. But when i used your code.Remaining spaces in the other columns are also getting compressed without space.

For ex:

"Alfred Nick"|" "|"14"|"69"|"112.5"   -  original line

after Compress with space

"AlfredNick"|""|"14"|"69"|"112.5"    -  Here Alfred Nick became AlfredNick(Space is compressed)

But I want as follow:

"Alfred Nick"|""|"14"|"69"|"112.5"  -  Only " " is stripped to "".

Please can you help me. Many Thanks.

[data_null__]

How about his.

_infle_ = transtrn(_infile_,'" "','""');

[Ksharp]

Oh. Here are two ways.

     data _null_;

infile "c:\x.csv" ;

file "c:\want.csv" ;

input;

*_infile_=prxchange('s/\|"\s+"\|/\|""\|',-1,_infile_); *<---first way;

_infile_=tranwrd(_infile_,'|" "|','|""|'); *<---- second way;

put _infile_;

run;

Ksharp

[shivas]

Hi,

You can try this code...taken from SAS DI...

data test;

input (abc bcd def efg) ($);

cards;

one two three four

five six seven eigth

;

run;

data _null_;

   set test;

     quote='"';

      file "F:\testing1.csv" dlm='|';

      if ( _n_ = 1 ) then

         do;

            put

               "abc|bcd|def|efg";

               end;

   put

      quote +(-1) abc +(-1) quote

      quote +(-1) bcd +(-1) quote

      quote +(-1) def +(-1) quote

      quote +(-1) efg +(-1) quote

            ;

run;

Thanks,

Shiva