Get back first non-null value for multiple columns grouped by some other columns

[Vivi1997]

How to obtain the right table as output when the left table is given as input. I want to group by prod and geo columns and fetch the first not null value for all the rules columns. Is there any way to do this using SAS Programming or Proc SQL query?

[andreas_lds]

Untested:

proc sql;
  create table want as
    select prod, geo, max(rule_1) as rule_1, max(rule_2) as rule_2 ....
      from have
      group by prod, geo;
quit;

[Vivi1997]

This will give me the max values for rules column however what I intend to fetch is the first not null value and it is not necessary that it might be max or min value for different rules. I tried this piece of sql code:
SELECT prod, geo,
(SELECT rule_1 FROM temp WHERE rule_1 IS NOT NULL LIMIT 1) as rule_1,
(SELECT rule_2 FROM temp WHERE rule_2 IS NOT NULL LIMIT 1) as rule_2,
(SELECT rule_3 FROM temp WHERE rule_3 IS NOT NULL LIMIT 1) as rule_3,
(SELECT rule_4 FROM temp WHERE rule_4 IS NOT NULL LIMIT 1) as rule_4
FROM temp
GROUP BY prod, geo
But somehow this is working only for first prod, geo combination and not working for subsequent prod, geo combinations

[andreas_lds · Accepted solution]

This step should be closer to your expectations, as @Patrick already said: if you want tested code, provide data in usable form.

data want;
   set have;
   by prod geo;
   
   array r[4] _temporary_;
   array rules rule_1 - rule_4;
   
   if first.geo then do;
      call missing(of r[*]);
   end;
   
   do i = 1 to dim(rules);
      r[i] = coalesce(r[i], rules[i]);
   end;
   
   if last.geo then do;
      do i = 1 to dim(rules);
         rules[i] = r[i];
      end;
      
      output;
   end;
   
   drop i;
run;

In the data you have show, the value of geo is constant for one value of prod, if this is not always the case, the result may differ from your expectations.

[Vivi1997]

Thanks a lot for this piece of code. It worked perfectly well for my data.

[Patrick]

@Vivi1997 If you're after tested code as answer then please provide sample data in the form of SAS data steps that create this data. 

Ideally also provide the desired result based on the logic explained and the source sample data provided.

[Ksharp]

/*Plz post the data step code, Nobody would like to type it for you !*/
data have;
input prod geo r1-r4;
cards;
301 3001 . 300 . 600
301 3001 100 . 500 700
301 3001 200 400 . .
302 3002  10 . 50 .
302 3002 . 20 . 80
302 3002 . 30 40 70
;

data temp;
 set have;
 by prod geo ;
 if first.geo then n=0;
 n+1;
run;
proc sort data=temp;by prod geo descending n;
data want(drop=n);
 update temp(obs=0) temp;
 by prod geo  ;
run;