Help using Base SAS procedures

How to choose the first observations of groups in SQL?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

How to choose the first observations of groups in SQL?

Hi All;

I have this kind of data set, in which I have multiple observations for each distinct name-date group. as follows:

     Name     date          VAR

     aaa          2000          1233

     aaa          2000          1233

     aaa          2000          1233

     aaa          2001           3545

     aaa          2001          3545

     aaa          2001           3545

     bbb          2000          1000

     bbb           2000          1000

     bbb           2001          2000

As it is apparent, the first three observations are the same. However I need to get just one for each name-date group in order to be able to do some statistical calculations with them.

. I mean, I need  some thing like this: (In other words, I want the first observation of each group)

      NAME    date         var

         aaa     2000       1233

         aaa     2001       3545

         bbb      2000       1000

          bbb     2001       2000

I am trying to write it in sql using select distinct. but I am not sure how to write this. Can some one please help me with it?

thanks a lot!


Accepted Solutions
Solution
‎11-13-2012 04:23 PM
Super User
Posts: 19,878

Re: How to get rid of duplicate observations in a specific way?

Posted in reply to Shayan2012

It looks the same to me...

Ideally you should control your join better so you don't get multiples unless you want them.

Are you looking for the following?

proc sql;

create table want as

select distinct name, date, var

from have;

quit;

View solution in original post


All Replies
Super User
Posts: 19,878

Re: How to get rid of duplicate observations in a specific way?

Posted in reply to Shayan2012

What would you like for var though? Max/min/random observation?

You can do it in sql with a group by

proc sql;

select name, date, max(var) as var

from have

group by name, date;

quit;

Or proc sort as always

Frequent Contributor
Posts: 75

Re: How to get rid of duplicate observations in a specific way?

Thanks a lot Reeza,

I think I must describe it more to become clearer. I want to caclulate statistical information for each observation (like mean, median, skewness, etc). But my problem is that since I got the data set from joining two tables, I have multiple observations for each name-date group, so if I use your code, it won't give me the correct answer. I edited the question for more specificity.

Solution
‎11-13-2012 04:23 PM
Super User
Posts: 19,878

Re: How to get rid of duplicate observations in a specific way?

Posted in reply to Shayan2012

It looks the same to me...

Ideally you should control your join better so you don't get multiples unless you want them.

Are you looking for the following?

proc sql;

create table want as

select distinct name, date, var

from have;

quit;

Frequent Contributor
Posts: 75

Re: How to get rid of duplicate observations in a specific way?

Thanks a lot Reeza. I think that's what I've been looking for.

Super Contributor
Posts: 1,636

Re: How to choose the first observations of groups in SQL?

Posted in reply to Shayan2012

data have;

input

Name$     date          VAR;

cards;

     aaa          2000          1233

     aaa          2000          1233

     aaa          2000          1233

     aaa          2001           3545

     aaa          2001          3545

     aaa          2001           3545

     bbb          2000          1000

     bbb           2000          1000

     bbb           2001          2000

  ;

proc sql;

  create table want as

    select distinct * from have;

  quit;

  proc print;run;

Frequent Contributor
Posts: 75

Re: How to choose the first observations of groups in SQL?

Thanks a lot linlin.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 291 views
  • 3 likes
  • 3 in conversation