I have a table with fields that are mixed numeric and character, and some of the fields are blank. I'm trying to CATX them with a '|' delimiter so that I can output it to a CSV file, but CATX is skipping blank fields. Is there a way around this? Below is a sample code of what I'm trying to do, but instead of axyz = "1|a|c", I want it to be "1|a||c" (extra | between a and c). Is there a way to do this without brute forcing it using CATS and listing every column?
data my_data;
input a x $ y $ z $;
datalines;
1 a . c
;
run;
proc transpose data=my_data(obs=0) out=temp;
var _all_;
run;
proc sql noprint;
select cats('strip(',_name_,')') into :catx separated by '||"|"||' from temp ;
quit;
data new_my_data;
set my_data;
length axyz $ 200;
axyz = &catx.;
run;
If you don't require using cat.. function, concatenation operator '||' could do this job;
data want;
set my_data;
axyz= a||'|'||x||'|'||y||'|'||z;
proc print;run;
What about using PROC EXPORT to create your CSV using "|" as the delimiter?
@EdCenter wrote:
Heh, so the wrinkle is that I have different tables with different columns that I have to interleave to create a single pseudo-EDI file for processing.
So for simplicity, I have 4 tables: Header, Member, Detail, Trailer table. Each with different columns. I need to interleave it like this:
Header
Member(1)
Detail(1)
...
Member(n)
Detail(n)
Trailer
What I'm trying to do with CATX is to concat everything into a single field, then interleave the records in each table (in the right order) into a flatfile.
This looks like basic report writing. Use BY group processing;
data _null_;
file out dsd ;
set have end=eof;
by member ;
if _n_=1 then put header;
if first.member then put member;
put details;
if eof then put trailer;
run;
Example:
128 data _null_; 129 set class end=eof; 130 by sex; 131 file log dsd dlm='|'; 132 if _n_=1 then put 'Report of SASHELP.CLASS by SEX'; 133 if first.sex then put sex; 134 put name -- weight; 135 if eof then put 'End of Report'; 136 run; Report of SASHELP.CLASS by SEX F Alice|F|13|56.5|84 Barbara|F|13|65.3|98 Carol|F|14|62.8|102.5 Jane|F|12|59.8|84.5 Janet|F|15|62.5|112.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 M Alfred|M|14|69|112.5 Henry|M|14|63.5|102.5 James|M|12|57.3|83 Jeffrey|M|13|62.5|84 John|M|12|59|99.5 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 End of Report
Then I would replace missing values with a rare special character (during concatenation using cat..) to replace them later by a missing value again using tranwrd function like below:
data want;
set my_data;
array vars [*] a--z;
do i=1 to dim(vars);
if vars{i}='' then vars{i}= '~';
end;
axyz= catx('|', of a--z);
if find(axyz, '~') gt 0 then axyz= tranwrd(axyz, '~', ' ');
do i= 1 to dim(vars);
if vars{i}='~' then vars{i}= '';
end;
drop i;
proc print;run;
Yes this is my other option. Change all blanks to "." and ask the development team to ignore fields with just ".". Was hoping to avoid this option.
As for the use of arrays, not all columns are string which is the other issue with the use of arrays (which I looked into).
Use the reverse of the INFILE trick. Call it the FILE trick.
filename dummy temp;
data want;
set sashelp.class;
length line $100;
file dummy dsd dlm='|' ;
put @1 100*' ' @1 name--weight @;
line=_file_;
run;
even more "lazy" version 😉
filename dummy temp;
data want;
set sashelp.class;
file dummy dsd dlm='|' ;
put @1 100*' ' @1 (_all_) (:) @;
length line $100;
line=_file_;
run;
data my_data;
input a x $ y $ z $;
datalines;
1 a . c
;
run;
proc transpose data=my_data(obs=0) out=temp;
var _all_;
run;
proc sql noprint;
select cats('strip(',_name_,')') into :catx separated by '||"|"||' from temp ;
quit;
data new_my_data;
set my_data;
length axyz $ 200;
axyz = &catx.;
run;
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.