Help using Base SAS procedures

sql issue

Reply
Occasional Contributor
Posts: 11

sql issue

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

PROC Star
Posts: 7,492

sql issue

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;

Occasional Contributor
Posts: 11

sql issue

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

Thanks again.

Ask a Question
Discussion stats
  • 2 replies
  • 118 views
  • 0 likes
  • 2 in conversation