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 |
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
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 */
Nested function calls.
catx(', ' , V1, V2, cats(of v3-v5))
Do you really want the space after the comma in the result? Why?
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
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
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;
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
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.