topic Re: CATX but don't skip blanks in SAS Programming

CATX but don't skip blanks

EdCenter — Wed, 06 Dec 2023 18:52:08 GMT

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;

Re: CATX but don't skip blanks

A_Kh — Wed, 06 Dec 2023 19:09:03 GMT

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;

Re: CATX but don't skip blanks

EdCenter — Wed, 06 Dec 2023 19:11:37 GMT

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?

Re: CATX but don't skip blanks

SASKiwi — Wed, 06 Dec 2023 19:21:48 GMT

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

Re: CATX but don't skip blanks

EdCenter — Wed, 06 Dec 2023 19:29:09 GMT

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.

Re: CATX but don't skip blanks

A_Kh — Wed, 06 Dec 2023 19:42:10 GMT

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;

Re: CATX but don't skip blanks

Tom — Wed, 06 Dec 2023 21:06:51 GMT

@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

Re: CATX but don't skip blanks

Tom — Wed, 06 Dec 2023 20:22:02 GMT

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;

Re: CATX but don't skip blanks

EdCenter — Wed, 06 Dec 2023 20:40:03 GMT

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

Re: CATX but don't skip blanks

EdCenter — Wed, 06 Dec 2023 20:58:42 GMT

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

Re: CATX but don't skip blanks

yabwon — Wed, 06 Dec 2023 21:37:09 GMT

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;

Re: CATX but don't skip blanks

Ksharp — Thu, 07 Dec 2023 05:44:05 GMT

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;

Re: CATX but don't skip blanks

EdCenter — Thu, 07 Dec 2023 17:27:42 GMT

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