topic Re: Concatenating within a datastep? in SAS Programming

Concatenating within a datastep?

genemroz — Fri, 14 Oct 2022 22:15:39 GMT

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

Re: Concatenating within a datastep?

Astounding — Fri, 14 Oct 2022 22:53:18 GMT

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

Re: Concatenating within a datastep?

genemroz — Sat, 15 Oct 2022 01:39:40 GMT

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

Re: Concatenating within a datastep?

Kurt_Bremser — Sat, 15 Oct 2022 05:18:13 GMT

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;

Re: Concatenating within a datastep?

ballardw — Sat, 15 Oct 2022 05:44:40 GMT

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

Re: Concatenating within a datastep?

genemroz — Sat, 15 Oct 2022 14:25:51 GMT

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.

Re: Concatenating within a datastep?

Tom — Mon, 17 Oct 2022 03:07:07 GMT

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.

Re: Concatenating within a datastep?

Tom — Mon, 17 Oct 2022 03:11:41 GMT

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

Re: Concatenating within a datastep?

genemroz — Mon, 17 Oct 2022 14:58:18 GMT

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