BookmarkSubscribeRSS Feed
newbie
Calcite | Level 5

I have a dataset as shown below, here for every repetitive records of EffectDate and ITAmnt i should retain

only the minimum BillNumb values. The below code works fine if i do not include the 'id' variable which is an auto

generated number.But i need to have this auto number in my final output, could anybody pls help me out in solving this issue?

data All_Bills;

  input id EffectDate $ ITAmnt BillNumb;

  cards;

1 John 7 1

5 Joe 12 3

6 Joe 12 2

7 Harry 13 2

9 Mary 11 4

10 Mary 11 3

;

PROC SORT DATA= All_Bills out=bills;

by EffectDate ITAmnt;

RUN;

proc sql;

select EffectDate, ITAmnt,min(BillNumb)as BillNumb from bills

group by EffectDate, ITAmnt

order by EffectDate, ITAmnt;

quit;

Final output should be sumthing like this:

1 John 7 1

6 Joe 12 2

7 Harry 13 2

10 Mary 11 3

2 REPLIES 2
art297
Opal | Level 21

Your sql statement doesn't appear to match your desired output AND there isn't any reason to use proc sort in this situation.  Does the following accomplish what you are looking to do?:

proc sql;

  select id, EffectDate, ITAmnt,BillNumb

    from all_bills

      group by EffectDate, ITAmnt

        having BillNumb=min(BillNumb)

          order by id

  ;

quit;

newbie
Calcite | Level 5

Thanks Art. This is what i expected and yeah proc sort was not required here.

Thanks again.

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
  • 2 replies
  • 587 views
  • 0 likes
  • 2 in conversation