11-18-2014 09:56 AM
How can I assign the first Rating value with a higher rank in a group to the entire group.
See below data set
if for a group rt_rank is 1 then I want to assign the first value rating to the entire group.
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!
11-18-2014 10:21 AM
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
11-18-2014 10:42 AM
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.
11-19-2014 09:07 AM
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;