Date:         Thu, 6 Oct 2011 11:11:12 -0700
Reply-To:     Irin later <irinfigvam@yahoo.com>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Irin later <irinfigvam@YAHOO.COM>
Subject:      Ranking query to assign an order of patients for each doctor
Content-Type: text/plain; charset=iso-8859-1

I have to create a  ranking query to assign an order of patients in the table MEMBERS who belong to the same PCP ID

pcp1 jen12        order1
pcp1 roy11       order2 
pcp1 alic32       order3  
pcp1 kate23      order4

pcp2 bill22           order1
pcp2 nany24        order2 
pcp2 stephany30  order3 

pcp3 john44    order1
...........................
and so on

Actually I created a numerical autokey field  as a sequence for the whole file while I need an addotional field with a sequence of patients for 
particular providers.
       
The following query  in SAS generates an error:

PROC SQL;
create table members as 
select mem.autokey, mem.membername, mem.pcpName,
(select count(*) 
FROM members m
where m.pcpID=members.pcpID and m.autokey<=members.autokey) as Order
From members;
quit;

ERROR: File WORK.MEMBERS.DATA does not exist.
ERROR: File WORK.MEMBERS.DATA does not exist.

How can I do it in SAS? Could you please give me a hand?

Thank you in advance,
Irin