how to apply delimiter based on condition

[1239]

Hi,

I need to apply delimiter (",") only when data is present in A and B but not for C, D and E variables. I am trying the below code but unable to achieve the desired output values. Kindly refer below SAS code, input data (.csv file attached) and desire output.

Please help me in achieving the desired output value.

data need;
set have;
V = cats(V1, ",", V2, ",", V3, V4, V5);
run;

Input data:

A	B	C	D	E
AB	CD	EF	GH	IJ
	MN		QR	ST
		EF	GH	IJ
AB		EF

desired output value

AB, CD, EFGHIJ

MN, QRST

EFGHIJ

AB, EF

[sbxkoenk]

data have;
LENGTH A B C D E $ 2;
infile datalines delimiter='#';
input A $ B	$ C	$ D $ E $;
datalines;
AB	#CD	# EF #GH #IJ
 	#MN	#    #QR #ST
 	# 	# EF #GH #IJ
AB	# 	# EF # 	 # 
;
run;

data want;
 LENGTH concat1 $ 10 concat2 $ 10 concat $ 20;
 set have;
 sp1=', ';
 sp2='@';
 concat1 = CATX(SP1, A, B);
 concat2 = CATX(SP2, C, D, E);
 if strip(concat1) ^= '' then do; concat = strip(concat1)!!', '!!strip(concat2); end;
 else                         do; concat = strip(concat2);                       end;
 concat=compress(concat,'@');
run;
/* end of program */

[Tom]

Nested function calls.  

 catx(', ' , V1, V2, cats(of v3-v5))

Do you really want the space after the comma in the result? Why?

[1239]

 @sbxkoenk and @Tom 

The code will result as " , MN, QRST" but I need MN, QRST. I don't want additional "," when data is empty. Please refer desired output value.

[Tom · Accepted solution]

Please show example data where you are getting extra comma.

data have;
  input (A B C D E) ($);
cards;
AB CD EF GH IJ
 . MN  . QR ST
 .  . EF GH IJ
AB  . EF  .  .
;

data want;
 set have;
 length string $20;
 string=catx(',',a,b,cats(c,d,e));
run;

Result

[1239]

Hello @Tom 

Your code works now and earlier it didn't work because that I was trying your code and @sbxkoenk code and dataset names were same.

Thanks for your help!

[sbxkoenk]

Hello @1239 ,

I'm sorry but it's not correct what you say. There's no extra comma.

See here a PROC PRINT output of the dataset WANT that I produce :

Koen

[1239]

Hello @sbxkoenk,

Please see the PROC PRINT below and extra "," is present in obs = 2 and 3.

data have;
LENGTH A B C D E $ 2;
infile datalines delimiter='#';
input A $ B	$ C	$ D $ E $;
datalines;
AB	#CD	# EF #GH #IJ
 	#MN	#    #QR #ST
 	# 	# EF #GH #IJ
AB	# 	# EF # 	 # 
;
run;

data want;
 LENGTH concat1 $ 10 concat2 $ 10 concat $ 20;
 set have;
 sp1=', ';
 sp2='@';
 concat1 = CATX(SP1, A, B);
 concat2 = CATX(SP2, C, D, E);
 if strip(concat1) ^= '' then do; concat = strip(concat1)!!', '!!strip(concat2); end;
 else                         do; concat = strip(concat2);                       end;
 concat=compress(concat,'@');
run;

proc print data= want;
run;

[sbxkoenk]

Hello @1239 ,

That's very weird. I do not have that extra comma when running that code (my code).

I use SAS 9.4 M7 (Maintenance Level 7).

Maybe you run another version (?).
Or maybe your values are not really missing, ... instead of "nothing" you may have spaces (??).
I can probably find the root cause for the difference, but since you already have the solution, I'm not going to break my head about it. 

Ciao,

Koen

[1239]

Hello @sbxkoenk,

I use SAS 9.4 M6. Thanks for all your help and I appreciate it!