Hello dear SAS community.
I have a real struggle at the moment, and I'm running out of time.
To make things short: I want for each year, rank all firms by X for that year. But only max "5 Ranks" fo reach year, and start in year 1968 for example, and repeat it each year.
For example:
FirmID YEAR VAR_X WANT
1000 1965 9 3 4 (69)
1000 1966 10 2 3(69)
1000 1967 11 1 2(69)
1000 1968 12 1(69)
1000 1969 16
1000 1970 19
1000 1971 20
1000 1972 21
.
XXXX XXXX XXXX XXXX
BUT the problem starts when I'm doing this in year 1969, since it contains a "rank" variable in pre 68's.
What precisely does the WANT value [ 3 4 (60) ] mean in the data line for 1965.
FirmID YEAR VAR_X WANT
1000 1965 9 3 4 (69)
It appears you want, for each year, to
This means you would want, for each year t, 4 RANK vars
Is that what you mean?
Also, what rank do you want to assign when there are ties in the x values?
Yes, this is what I want. Sorry for bad "writing" about it 🙂
Sorry for asking rude, but can you help me about it?:)
I think it may help to provide a little more raw input data and how you want the result at the end of the process. You say " for each year" but your example only has one year of ranks (apparently).
Also please explain where you got your "want" values from. If 3 is supposed to be Rank, how did you get 3? The value of 9 for VAR_x is the smallest shown so I would expect it to be either 1 (if you want to rank or increasing values of var_x) or not ranked if looking at decreaseing values. And what is 4?
How do you get 2 values of 1? Are you perhaps missing a bunch of different variables to hold multiple periods of consideration but didn't show them?
The DATA WANT step below does what I think you want. If there are ties among the X's being ranked, then it produces the average of the lowest and highest rank assigned to the X value:
It works by:
data have;
input FirmID YEAR X;
output;
firmid=2000; if mod(_n_,2)=0 then x=x-2; output;
datalines;
1000 1965 9
1000 1966 10
1000 1967 11
1000 1968 12
1000 1969 16
1000 1970 19
1000 1971 20
1000 1972 21
run;
proc sort; by firmid year;run;
data want (drop=_:);
array _x{1960:2010} _temporary_;
set have (keep=firmid year x );
by firmid;
if first.firmid then call missing(of _x{*});
_x{year}=x; /* Fill the _X array */
label rank_0 = "Rank of current X for Current Year-3 through Current Year"
rank_1 = "Rank of current X for Current Year-2 through Current Year+1"
rank_2 = "Rank of current X for Current Year-1 through Current Year+2"
rank_3 = "Rank of current X for Current Year through Current Year+3" ;
array rnk{0:3} rank_0-rank_3;
if last.firmid then do until (last.firmid);
set have ;
by firmid;
do _r=0 to 3;
do _rank_beg=1 by 1 until (largest(_rank_beg ,_x{year+_r-3},_x{year+_r-2},_x{year+_r-1},_x{year+_r-0})=x);end;
if _rank_beg<4 then do _rank_end=_rank_beg to 3 while (largest(_rank_end+1,_x{year+_r-3},_x{year+_r-2},_x{year+_r-1},_x{year+_r-0})=x);end;
else _rank_end=4;
rnk{_r}=(_rank_beg+_rank_end)/2;
end;
output;
end;
run;
Editted addition. To deal with possible ties for X, you see two consecutive loops
DO _RANK_BEG= 1 by 1 until (.....);
IF _RANK_BEG<4 then DO _RANK_END=_RANK_BEG to 3 WHILE (.....);
If you expect no ties in X, you could replace them with a single loop:
DO RNK{_r}=1 by 1 until (largest(rnk{_r},_x{year+_r-3},_x{year+_r-2},_x{year+_r-1},_x{year+_r-0})=x);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.