Concatenate values by other values

[Mathis1]

Hello,

Please find attached the table that will illustrate my problem. I'd like that for each value of my variable PUIS_CLASS, I have the variable NEW which is the concatenation of all the values of GROUPEACT occuring when the  PUIS_CLASS value is observed.

For instance, the two first lines would be :

PUISS_CLASS         GROUPEACT

1_K                            "20_24_25_26_27_28"

2_L                             "24_25_27" 

etc...

Thank you for your help 😄 

[PaigeMiller]

In a SAS DATA step:

want = cats(puiss_class,groupeact);

[Mathis1]

Unfortunately, that is not what i want 😕 

[PaigeMiller]

---

@Mathis1 wrote:

Unfortunately, that is not what i want 😕 

---

Then explain what you do want. Your original message never showed the desired output.

[ballardw]

data want;
   set have;
   by puiss_class;
   length group $ 200;
   retain group;
   if first.puiss_class then call missing(group);
   value=catx('_',group, groupeact);
  if last.puiss_class;
  drop groupeact;
run;

Since we need to reference two variable, the combined version and the starting version we cannot place the results directly into Groupeact as you imply. For one thing the existing variable is too short hold the combined values. You can adjust the length of the Group variable to be a better fit, or longer if needed.

[mklangley]

This works, though there may be a simpler way:

(Edit: Per your original question, modified to use NEW as the target variable.)

proc sort data=hello_sas;
    by puis_class;
run;

data hello_sas_concat;
    set hello_sas;
    by puis_class;

    retain concat;
    
    if first.puis_class then
        concat = cats(groupeact, "");

    else if not(last.puis_class) then
        concat = cats(concat, "_", groupeact);

    else
        NEW = cats(concat, "_", groupeact);
    
    drop concat;
run;

data want (keep=puis_class NEW);
    set hello_sas_concat;
    where NEW is not null;
run;

[ed_sas_member · Accepted solution]

Hi @Mathis1 

Please try this:

proc sort data=hello_sas;
	by PUIS_CLASS;
run;

data want;
	set hello_sas;
	by PUIS_CLASS;
	length GROUPEACT2 $ 100;
	retain GROUPEACT2;
	if first.PUIS_CLASS then GROUPEACT2 = GROUPEACT;
	else GROUPEACT2 = catx("_", GROUPEACT2, GROUPEACT);
	if last.PUIS_CLASS then output;
	drop GROUPEACT;
	rename GROUPEACT2=GROUPEACT;
run;

[novinosrin]

data need;
 do until(last.PUIS_CLASS);
  set hello_sas;
  by PUIS_CLASS  notsorted;
  length GROUPEACT_  $100;
  GROUPEACT_=catx('_', GROUPEACT_, GROUPEACT);
 end;
 drop GROUPEACT;
 rename GROUPEACT_=GROUPEACT;
run;

[biopharma]

Unlike others, this is a multi step solution but I have kept it simple. You may have to sort your dataset first by PUIS_CLASS.

proc transpose 
     data = have 
     out=Wanting 
     prefix=_c ;
    by puis_class ;
    var groupeact ;
run ;

data want ;
   set wanting ;
   length groupeact $2000 ;
   groupeact = quote(catx(",", of _c:)) ;
   drop _: ;
run ;

[biopharma]

I used the wrong separator value. This line should read:
groupeact = quote(catx("_", of _c:)) ;