Concatenating within a datastep?

[genemroz]

Esteemed Advisers:

I'm stuck on a problem of how to concatentate text variable within a datastep.

Below is a simplified version of the problem and my failed attempt to solve it.

This is what I hope to achieve:

data want
key k concat_text
1 3 US0001/US0002/US0003
2 3 US0004/US0005/US0006
3 2 US0001/US0005
4 2 US0002/US0006
5 1 US0003

data HaveA;
infile datalines delimiter=',';
length key 8. k 8.;
input key k;
datalines;
1,0
2,0
3,0
4,0
5,0
;
run;

data HaveB;
infile datalines delimiter=',';
length key 8. k 8. text $6;
input key k text;
datalines;
1,1,US0001
1,1,US0002
1,1,US0003
2,1,US0004
2,1,US0005
2,1,US0006
3,1,US0001
3,1,US0005
4,1,US0002
4,1,US0006
5,1,US0003
;
run;

data want (keep=key k concat_text);
update havea (in=a) haveb(in=b rename=k=kb);
length concat_text $100;
by key;
retain concat_text;
if a and b then do
concat_text=trim(left(concat_text))||trim(left(text));
k=k+kb;
end;
run;

Clearly I'm using the RETAIN statment incorrectly.  And I don't know how to introduce the delimiter in the concatenated variable.  Thanks in advance for your help with this.

Gene

[Astounding]

You are using an UPDATE statement.  Do you know how that works?

[genemroz]

Yes. I've used Update before.  I think it suits my needs.  And I think I know how it works.  But if there's a better or different way, I'm open to other approaches.

Thanks,

Gene

[ballardw · Accepted solution]

---

@genemroz wrote:

Yes. I've used Update before.  I think it suits my needs.  And I think I know how it works.  But if there's a better or different way, I'm open to other approaches.

Thanks,

Gene

---

Update works for this case. BUT

1) you need to reset the accumulated text for a new key

2) and Catx will place the / and strip any leading/trailing spaces in the concatenation.

data want (keep=key k concat_text);
   update havea (in=a) haveb(in=b rename=k=kb);
   length concat_text $100;
   by key;
   retain concat_text;
   if first.key then call missing(concat_text);
   if a and b then do
         concat_text=catx('/',concat_text,text);
   k=k+kb;
end;

[genemroz]

Both solutions worked but I'm marking this as "accepted" because it works within the context of using the UPDATE statement. Many thanks to both Advisers for their time and effort to help me with this.

[Kurt_Bremser]

Use a MERGE:

data want (keep=key k concat_text);
merge
  havea (in=a rename=(k=ka))
  haveb (in=b rename=(k=kb))
;
length k 8 concat_text $100;
by key;
if a and b;
retain concat_text k;
if first.key
then do;
  concat_text = text;
  k = ka;
end;
else concat_text = catx(",",concat_text,text);
k + kb;
if last.key;
run;

[Tom]

Why would you use update when you have NO variables to update?

Your base dataset only has two variables, one of which is  the ID variable.  So only variable that could be updated is K.

Your transaction dataset has three variables, but after you rename K to KB then NONE of them will UDPATE the one non ID variable in the base dataset.

So you are essentially just doing a MERGE with an implied IF LAST.KEY statement at the end.

[Tom]

Looks like you just want to SET those two dataset together.

data want;
  do until(last.key);
    set havea haveb ;
    by key;
    length sum_k 8 concat_text $100 ;
    sum_k=sum(sum_k,k);
    concat_text=catx('/',concat_text,text);
  end;
run;

Result:

Obs    key    k     text     sum_k    concat_text

 1      1     1    US0003      3      US0001/US0002/US0003
 2      2     1    US0006      3      US0004/US0005/US0006
 3      3     1    US0005      2      US0001/US0005
 4      4     1    US0006      2      US0002/US0006
 5      5     1    US0003      1      US0003

[genemroz]

Thanks for this solution. It shows that, together with the other two proposed solutions, there are often multiple possible solutions with SAS for any given problem.
Regards,
Gene