Date: Thu, 22 Sep 2011 11:24:59 -0400
Reply-To: Tom Smith <need_sas_help@YAHOO.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Tom Smith <need_sas_help@YAHOO.COM>
Subject: A PROC SQL using Count or Rank function - Very Urgent
4 of you had already post the best solution. But unfortuantely it did not
work because I am trying
to use T-SQL comparing to Proc SQL and they do not share every functions
common.
So I am planning a different approach Please help me out looking below. My
job may depend
on this in the tough time.
I have the following dataset withe three variable
providerid (character) provsec (numeric) specification (character)
and the sample dataset is as below:
providerid provsec specification
---------- ------- -------------
2101 9 family
2101 19 society
2101 8 joubon
2101 2 sarkar
2101 11 lost
212222 10 saver
212222 9 family
212333 10 saver
214555 50 net
I need a dataset using only Proc SQL ( using no macro, no array - the
reason is I am going try to
apply it in SQL Programming. I know they are not same, but atleast I gotta
try.) This is a sample
of a very large dataset. if any providerid has more than one record the
next record goes to look_2,
look_3, looK_4, Look_5. Please use either Count or rank function.
Please find the result as below (please look at th erank variable
carefully)
providerid provsec specification rank
---------- ------- ------------- ----
2101 9 family look_1
2101 19 society look_2
2101 8 joubon look_3
2101 2 sarkar look_4
2101 11 lost look_5
212222 10 saver look_1
212222 9 family look_2
212333 10 saver look_1
214555 50 net look_1
