How can I assign the first Rating value with a higher rank in a group to the entire group.

Reply
New Contributor
Posts: 4

How can I assign the first Rating value with a higher rank in a group to the entire group.

How can I assign the first Rating value with a higher rank in a group to the entire group.

See below data set

IDRatingPriceRt_Rank
ANB 1050
ANB32001
IGA27050
IGA7001
IGHY1021
IGNR10051
RSHY201
AKNR8030
DCA00
DCNR120000

if for a group rt_rank is 1 then I want to assign the first value rating to the entire group.

Desired dataset:

IDRatingPriceRt_RankRating_grp
ANB 1050B3
ANB32001B3
IGA27050A
IGA7001A
IGHY1021A
IGNR10051A
RSHY201HY
AKNR8030NR
DCA00NR
DCNR120000NR

I achieved this rating_grp by creating a intermediate dataset and creating a temporary variable with this first rating value and then merge it with the original dataset.

But I want to know if there is a alternate and easy way to do this.

Thanks in advance!

Super User
Super User
Posts: 7,942

Re: How can I assign the first Rating value with a higher rank in a group to the entire group.

Well its a merge at some point (or hash).  A question though, in your example data you set Rating_grp as A for IG group, based on (I presume) order of occurrence in the data you get.  Is this really how you want to do things?  For instance if that data gets manipulated or sorted at some point you may get completely different results.  Seems a bit wolly to me.  I would want to explicitly state an order for the data, then pull out the first *in that specific order* to be merged back to the original data, that way you can consistently state what your results will be.  The other alternative is to allocate an order number to your data and use that:

ID Rating Price Rt_Rank Rating_grp  N

AN B  105 0 B3 1

AN B3 200 1 B3 2

IG A2 705 0 A 3

IG A 700 1 A 4

IG HY 102 1 A 5

IG NR 1005 1 A 6

New Contributor
Posts: 4

Re: How can I assign the first Rating value with a higher rank in a group to the entire group.

Hi,

Thanks for the reply!

Yes I want the data to be calculated this way, and the chances of it getting manipulated are less as the data is sorted in a previous step and hence will be in the same order for all occurances.

Your first suggestion- I am already usnig it that way but I am looking for  a alternate way.

Second suggestion is not clear to me: how can allocating a order 'N' will be helpful as I need to assign the rating per group based on the first rank in the group. 

Super User
Posts: 10,020

Re: How can I assign the first Rating value with a higher rank in a group to the entire group.


data have;
input ID $ Rating $ Price Rt_Rank ;
cards;
AN     B     105     0
AN     B3     200     1
IG     A2     705     0
IG     A     700     1
IG     HY     102     1
IG     NR     1005     1
RS     HY     20     1
AK     NR     803     0
DC     A     0     0
DC     NR     12000     0
;
run;


data want;
 do until(last.id);
  set have ;
  by id notsorted;
  if Rt_Rank=1 and not found then do; flag=Rating;found=1;end;
 end;
 if not found then flag=Rating;
 do until(last.id);
  set have;
  by id notsorted;
  output;
 end;
run;

Xia Keshan

Ask a Question
Discussion stats
  • 3 replies
  • 172 views
  • 0 likes
  • 3 in conversation