topic Re: Looping through column in New SAS User

Looping through column

RedBishop — Fri, 20 Mar 2020 10:21:54 GMT

Dear community,

I was hoping for a bit of help with a simple question. I have a dataset with two columns such as this:

Column1 Column2

A 123

A .

A 123

A 456

B .

B 789

All I'm looking for is to fill in the missings of Column2 with the mode by Column 1, like this:

Column1 Column2

A 123

A 456

B 789

I know that it's possible with a select distinct and left join in proc sql, but since I am an R user, I'd highly appreciate a loop solution.

In R, one solution is this:

for(X in unique(Data[,"Column1"]))

{

Data[Data[,"Column1"]==X&is.na(Data[,"Column2"]),"Column2"] <- which.max(table(Data[Data[,"Column1"]==X,"Column2"]))

}

Surely there's some equivalent in SAS?

Thanks a lot!

Re: Looping through column

PaigeMiller — Fri, 20 Mar 2020 10:30:15 GMT

Please explain the algorithm that replaces the missing A with 123 and the missing B with 789. What if the missing A was underneath a 123 and above a 456, what would the algorithm do in that case?

Re: Looping through column

RedBishop — Fri, 20 Mar 2020 10:32:25 GMT

It takes the mode (most common observation) of Column2, grouped by Column1.

Re: Looping through column

PeterClemmensen — Fri, 20 Mar 2020 10:33:13 GMT

Try this

data have;
input Column1 $ Column2;
datalines;
A 123
A .
A 123
A 456
B .
B 789
;

data want (drop=c);
   do _N_=1 by 1 until (last.Column1);
      set have;
      by Column1;
      c = max(c, Column2);
   end;
   do _N_=1 to _N_;
      set have;
      Column2 = c;
      output;
   end;
run;

Re: Looping through column

PaigeMiller — Fri, 20 Mar 2020 10:39:58 GMT

Most common observation is the mode, but if there are ties (for example two records with 123 and two records with 456) SAS will pick one arbitrarily.

proc summary data=have;
    class column1;
   var columns;
    output out=mode mode=mode;
run;
data want;
    merge have mode;
    if missing(column2) then column2=mode;
run;

Adding: I see you have marked an answer as correct, but it does not seem to compute the "most common observation". So which is it?

Re: Looping through column

RedBishop — Fri, 20 Mar 2020 10:42:22 GMT

The answer of draycut uses "max" instead of "mode", but correct enough for me.