CODEWITHSUNDEEP
group-bysasproc-sql
student_t
student_t

Reputation: 243

Collapsing data in SAS with PROC SQL

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.

Upvotes: 3

Views: 881

Answers (1)

Reeza
Reeza

Reputation: 21274

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;

Upvotes: 3

Related Questions