BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.

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?

 

Vivi1997_0-1664170181824.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

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.

View solution in original post

6 REPLIES 6
andreas_lds
Jade | Level 19

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
SAS Employee
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
Jade | Level 19

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
SAS Employee
Thanks a lot for this piece of code. It worked perfectly well for my data.
Patrick
Opal | Level 21

@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
Super User
/*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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1883 views
  • 3 likes
  • 4 in conversation