Date:         Thu, 22 Sep 2011 11:49:10 -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:      Re: A complex Data manupulation using only PROC SQ L - VERY VERY
              URGENT (Thank You)
Comments: To: Sate Songhor-Abadi <whrads@HOTMAIL.COM>

On Thu, 22 Sep 2011 09:21:32 -0400, Sate Songhor-Abadi
<whrads@HOTMAIL.COM> wrote:

>trust this version is for your data , may
>end up with redundant column like lum_6 if maximum
>count of specification is not known.
>
>
>
>
>data testds;
>input providerid  $ provsec  specification $ ;
>datalines;
>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
>;
>
>proc sql; create table testds as
>select *
>, monotonic() as NUMB
>from testds
>;
>
>proc sql; create table testds as
>select *
>, min(NUMB)as minnumb
>, 1+numb-min(NUMB) as ranknumb
>from testds
>group by providerid
>order by numb
>;
>
>
>proc sql; create table testds as
>select *
>, ifc(ranknumb=1, specification , '') format $4. as lum_1
>, ifc(ranknumb=2, specification , '') format $4. as lum_2
>, ifc(ranknumb=3, specification , '') format $4. as lum_3
>, ifc(ranknumb=4, specification , '') format $4. as lum_4
>, ifc(ranknumb=5, specification , '') format $4. as lum_5
>, ifc(ranknumb=6, specification , '') format $4. as lum_6
>from testds
>order by numb
>;

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