topic sql issue in SAS Procedures

sql issue

newbie — Sun, 19 Feb 2012 17:27:59 GMT

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

sql issue

art297 — Sun, 19 Feb 2012 17:37:17 GMT

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;

sql issue

newbie — Sun, 19 Feb 2012 17:49:05 GMT

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

Thanks again.