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!
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;
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
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.
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;
Thanks a lot Reeza. I think that's what I've been looking for.
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;
Thanks a lot linlin.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.