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

How to choose the first observations of groups in SQL?

Shayan2012 — Tue, 13 Nov 2012 20:35:33 GMT

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 2001 3545

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!

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

Reeza — Tue, 13 Nov 2012 20:38:43 GMT

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

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

Shayan2012 — Tue, 13 Nov 2012 21:19:13 GMT

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.

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

Reeza — Tue, 13 Nov 2012 21:23:20 GMT

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;

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

Linlin — Tue, 13 Nov 2012 22:57:56 GMT

data have;

input

Name$ date VAR;

cards;

aaa 2000 1233

aaa 2001 3545

bbb 2000 1000

bbb 2001 2000

;

proc sql;

create table want as

select distinct * from have;

quit;

proc print;run;

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

Shayan2012 — Thu, 15 Nov 2012 19:53:29 GMT

Thanks a lot linlin.

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

Shayan2012 — Thu, 15 Nov 2012 19:54:26 GMT

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