CATX but don't skip blanks

[EdCenter]

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;

data new_my_data;

set my_data;

length axyz $16;

axyz = catx('|', of a--z);

run;

[A_Kh]

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; 

[EdCenter]

Thanks, so yes this is one option but I would have to apply to several tables with different column names.
So is there a way to use the "a--z" functionality in SAS to concat all of these fields?

[SASKiwi]

What about using PROC EXPORT to create your CSV using "|" as the delimiter? 

[EdCenter]

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.

[Tom]

---

@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

[A_Kh]

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; 

[EdCenter]

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).

[Tom]

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;

[EdCenter]

Never seen _FILE_ before.. It looks like it might help. Thanks, I think the solution is to use PUT & @. Let me work with your sample and see if I can make it work..

[yabwon]

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;

[Ksharp · Accepted solution]

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;

[EdCenter]

Wow, that worked.. I'll have to learn how you did that with "separated by". Thanks!