Collapsing data in SAS with PROC SQL

user_8161508 0
Viewed 578

I've been trying unsuccesfully for sometime now to collapse a data set using a PROC SQL with GROUPBY and was wondering if I could get some help. Here is an example of what I am trying to do. Suppose we have the following data:

id   year  parent_id  age
"01" 1990    "23"      17
"01" 1991    "23"      18
"01" 1992    "23"      19
"02" 1978    "18"      24
"02" 1979    "18"      25

that we wanted to collapse by id preserving the row with the min age across years to get the following dataset

id   year   parent_id   age
"01" 1990   "23"        17
"02" 1978   "18"        24

I tried something along the lines of

proc sql;
    CREATE TABLE output_tablename as
       SELECT DISTINCT id, year, parent_id, min(age) as age
       FROM input_tablename
       GROUPBY id;
quit;

to no avail.

proc-sql sas group-by
1 Answers

You can use the HAVING clause to pick only records where age = min(age).

   proc sql;
   create table want as
   select * from have
   group by ID
   having age=min(age);
   quit;

PROC SORT option:

proc sort data=have; by id descending age;
run; 
proc sort data=have nodupkey out=want; 
by id;
run;
user_19195830
Related