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

Hi Guys,

I am noticing some strange row duplication when using the following min query (in bold) :

proc sql;

create table  TEST as

select        ID,

              min(FIRST_Date, information_date) as First_V2,

              max(LAST_Date)as Last_V2,

from          base

group by      ID;

quit;

Is this wrong syntax? It looks like sas is not getting the min value for information_date...

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

There shouldn't be a comma at the end of the max(etc. etc) line and, if you don't want duplicates, add a distinct statement.  e.g.:

data base;

  informat first_date information_date last_date date9.;

  input id first_date information_date last_date;

  cards;

1  29apr2012 30apr2012 15aug2012

1  30apr2012 29apr2012 14aug2012

2  29apr2012 30apr2012 15aug2012

3  30apr2012 29apr2012 14aug2012

;

proc sql;

create table  TEST as

select        DISTINCT ID,

              min(min(FIRST_Date, information_date)) as First_V2 format=date.,

              max(LAST_Date)as Last_V2 format=date9.

from          base

group by      ID;

quit;

View solution in original post

10 REPLIES 10
art297
Opal | Level 21

There shouldn't be a comma at the end of the max(etc. etc) line and, if you don't want duplicates, add a distinct statement.  e.g.:

data base;

  informat first_date information_date last_date date9.;

  input id first_date information_date last_date;

  cards;

1  29apr2012 30apr2012 15aug2012

1  30apr2012 29apr2012 14aug2012

2  29apr2012 30apr2012 15aug2012

3  30apr2012 29apr2012 14aug2012

;

proc sql;

create table  TEST as

select        DISTINCT ID,

              min(min(FIRST_Date, information_date)) as First_V2 format=date.,

              max(LAST_Date)as Last_V2 format=date9.

from          base

group by      ID;

quit;

Tom
Super User Tom
Super User

You seem to have confused the MIN aggregation syntax of SQL with the MIN() function syntax of SAS.

You asked it to set First_V2 to the minimum of two variables on the current observation and LAST_V2 to the maximum of LAST_Date over the whole dataset.  If you want to instead find the aggregate min over all the variables then use min(min(a,b)) or min(min(a),min(b)).

art297
Opal | Level 21

First, you were absolutely correct, thus I've added the min of min to my original post.  But, more importantly, would you please respond to this post so that I can be the first to congratulate you on attaining the extremely well deserved master status.  I can't believe it has taken this long as you've been a SAS Master well before this forum was created!

Tom
Super User Tom
Super User

Thanks Art.

Linlin
Lapis Lazuli | Level 10

I am the first. Congratulation TomSmiley Happy!  Sorry ArtSmiley Wink

TomKari
Onyx | Level 15

Woo-hoo! A SAS Master!

Does this mean you have to buy us all beer?

Congrats!

  Tom

Tom
Super User Tom
Super User

Thanks.

data _null_;

  do _n_= 99 to 1 by -1;

    put _n_ 'bottles of beer on the wall';

  end;

run;

Haikuo
Onyx | Level 15

Tom, LOL, and many many congrats! I wish it was Friday night so I could really use some of your beers.

Haikuo

PGStats
Opal | Level 21

More than deserved! Congrats Tom! Now it's official.

PG

PG
vomer
Obsidian | Level 7

Thanks Art & Tom. This explanation helps me a lot! As I suspected it was a syntax error.

Also congrats to Tom! you guys are so talented and helpful Smiley Happy Keep up the good work!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 10 replies
  • 26748 views
  • 3 likes
  • 7 in conversation