topic Concatenate values by other values in SAS Data Management

Concatenate values by other values

Mathis1 — Thu, 28 May 2020 15:30:24 GMT

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 😄

Re: Concatenate values by other values

PaigeMiller — Thu, 28 May 2020 15:40:50 GMT

In a SAS DATA step:

want = cats(puiss_class,groupeact);

Re: Concatenate values by other values

Mathis1 — Thu, 28 May 2020 15:54:26 GMT

Unfortunately, that is not what i want 😕

Re: Concatenate values by other values

ballardw — Thu, 28 May 2020 16:17:05 GMT

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.

Re: Concatenate values by other values

mklangley — Thu, 28 May 2020 18:49:43 GMT

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;

Re: Concatenate values by other values

ed_sas_member — Thu, 28 May 2020 16:24:57 GMT

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;

Re: Concatenate values by other values

novinosrin — Thu, 28 May 2020 16:25:58 GMT

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;

Re: Concatenate values by other values

PaigeMiller — Thu, 28 May 2020 16:36:29 GMT

@Mathis1 wrote:

Unfortunately, that is not what i want 😕

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

Re: Concatenate values by other values

biopharma — Thu, 28 May 2020 16:58:12 GMT

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 ;

Re: Concatenate values by other values

biopharma — Thu, 28 May 2020 17:01:29 GMT

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