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 |
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"
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"
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
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
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.
How about his.
_infle_ = transtrn(_infile_,'" "','""');
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.