BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Shayan2012
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Reeza
Super User

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

Shayan2012
Quartz | Level 8

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.

Reeza
Super User

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;

Shayan2012
Quartz | Level 8

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

Linlin
Lapis Lazuli | Level 10

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;

Shayan2012
Quartz | Level 8

Thanks a lot linlin.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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