DATA Step, Macro, Functions and more

Text File with Text Qualifier " and Field Delimiter |

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Text File with Text Qualifier " and Field Delimiter |

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 |


Accepted Solutions
Solution
‎09-11-2012 11:48 AM
Respected Advisor
Posts: 3,777

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

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"

View solution in original post


All Replies
Solution
‎09-11-2012 11:48 AM
Respected Advisor
Posts: 3,777

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

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"

New Contributor
Posts: 3

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

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

Super User
Posts: 9,662

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

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

New Contributor
Posts: 3

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

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.


Respected Advisor
Posts: 3,777

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

How about his.

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

Super User
Posts: 9,662

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

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

Super Contributor
Posts: 349

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 3996 views
  • 0 likes
  • 4 in conversation