topic Re: assign the same value by group in SAS Programming

assign the same value by group

Jedrzej — Tue, 09 Feb 2021 14:44:05 GMT

ID	var1	var2	var3
ID 00		Y
ID 00
ID 00	Y
ID 01	Y		Y
ID 01

Hi i have this table and I want to assign the same value to all vars by ID if I find "Y"

so it should be like

ID	var1	var2	var3
ID 00	Y	Y
ID 00	Y	Y
ID 00	Y	Y
ID 01	Y		Y
ID 01	Y		Y

thank you for your help

Re: assign the same value by group

Shmuel — Tue, 09 Feb 2021 14:58:28 GMT

Try next code:

data want;
 set have;
  by ID;  /* assumed data is sorted by ID */
      retain v1 v2 v3;  /* or v1-v3 */
      if first.ID then do;
         v1=var1; v2=var2; v3=var3; /* can be done by an array and a loop */
      end;
      var1=v1; var2=v2; var3=v3;
run;

Re: assign the same value by group

PeterClemmensen — Tue, 09 Feb 2021 15:01:51 GMT

There you go

data have;
infile datalines dlm=',' dsd;
input ID $ var1 $ var2 $ var3 $;
datalines;
ID 00, ,Y,  
ID 00, , ,  
ID 00,Y, ,  
ID 01,Y, ,Y 
ID 01, , ,  
;

data want;
   update have(obs=0) have;
   by ID;
   output;
run;

Result:

ID     var1  var2  var3 
ID 00        Y       
ID 00        Y       
ID 00  Y     Y       
ID 01  Y           Y 
ID 01  Y           Y

Re: assign the same value by group

Peter_C — Tue, 09 Feb 2021 15:07:15 GMT

I don't understand how that update statement knows that VAR1 should be set to Y on the first row ouput.
Same problem for row4 and VAR3

Re: assign the same value by group

Peter_C — Tue, 09 Feb 2021 15:09:21 GMT

Row4 is not at same problem as Row1. Sorry

Re: assign the same value by group

Astounding — Tue, 09 Feb 2021 15:39:52 GMT

Here's an approach. You might have to adjust for the actual variables you have in real life, and which ones should be replicated across observations:

data want;
   do until (last.id);
      update have(obs=0) have;
      by ID;
   end;
   do until (last.id);
      set have (keep=id);
      by id;
      output;
   end;
run;

The top loop accumulates the variable values, then the bottom loop outputs the appropriate number of times.

Re: assign the same value by group

PeterClemmensen — Tue, 09 Feb 2021 17:00:55 GMT

@Jedrzej My bad, I misread the question. @Astoundings correction is the way to go.

Re: assign the same value by group

Ksharp — Wed, 10 Feb 2021 12:40:45 GMT

If data like this , you code wouldn't work.

data have;
infile datalines dlm=',' dsd;
input ID $ var1 $ var2 $ var3 $;
datalines;
ID 00, , , 
ID 00, ,Y,   
ID 00,Y, ,  
ID 01,Y, ,Y 
ID 01, , ,  
;

Re: assign the same value by group

PeterClemmensen — Wed, 10 Feb 2021 12:42:12 GMT

As stated below, I am aware 🙂 @Astoundings correction does the trick

Re: assign the same value by group

ramgouveia — Wed, 19 Apr 2023 09:58:42 GMT

@Astounding

And if instead of one variable to group by I have two or more?

Thnk you.

Re: assign the same value by group

Astounding — Thu, 20 Apr 2023 16:14:04 GMT

It depends on what you mean when you say you have two or more variables. So give an example. Certainly the BY statement supports multiple variables, but the outcome might not be what you have in mind.