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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.