topic Re: Text File with Text Qualifier " and Field Delimiter | in SAS Programming

Text File with Text Qualifier " and Field Delimiter |

vrs_431 — Tue, 11 Sep 2012 14:58:19 GMT

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 |

Re: Text File with Text Qualifier " and Field Delimiter |

data_null__ — Tue, 11 Sep 2012 15:47:59 GMT

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"

Re: Text File with Text Qualifier " and Field Delimiter |

shivas — Thu, 13 Sep 2012 04:42:45 GMT

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

Re: Text File with Text Qualifier " and Field Delimiter |

vrs_431 — Mon, 24 Sep 2012 07:31:08 GMT

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

Re: Text File with Text Qualifier " and Field Delimiter |

Ksharp — Tue, 25 Sep 2012 04:49:40 GMT

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

Re: Text File with Text Qualifier " and Field Delimiter |

vrs_431 — Mon, 01 Oct 2012 09:36:23 GMT

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.

Re: Text File with Text Qualifier " and Field Delimiter |

data_null__ — Mon, 01 Oct 2012 10:55:42 GMT

How about his.

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

Re: Text File with Text Qualifier " and Field Delimiter |

Ksharp — Mon, 01 Oct 2012 14:25:57 GMT

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