topic Re: how to apply delimiter based on condition in SAS Programming

how to apply delimiter based on condition

1239 — Thu, 26 May 2022 14:34:12 GMT

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

Re: how to apply delimiter based on condition

sbxkoenk — Thu, 26 May 2022 14:55:16 GMT

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 */

Re: how to apply delimiter based on condition

Tom — Thu, 26 May 2022 14:51:51 GMT

Nested function calls.

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

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

Re: how to apply delimiter based on condition

1239 — Thu, 26 May 2022 15:59:10 GMT

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

Re: how to apply delimiter based on condition

Tom — Thu, 26 May 2022 16:07:57 GMT

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

Re: how to apply delimiter based on condition

sbxkoenk — Thu, 26 May 2022 18:15:06 GMT

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

Re: how to apply delimiter based on condition

1239 — Fri, 27 May 2022 03:58:44 GMT

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;

Re: how to apply delimiter based on condition

1239 — Fri, 27 May 2022 04:01:40 GMT

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!

Re: how to apply delimiter based on condition

sbxkoenk — Fri, 27 May 2022 10:05:39 GMT

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

Re: how to apply delimiter based on condition

1239 — Fri, 27 May 2022 10:35:35 GMT

Hello @sbxkoenk,

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