topic Re: replace lower percentage of row by the greater percetange of row in each group in SAS Programming

replace lower percentage of row by the greater percetange of row in each group

Alexxxxxxx — Tue, 01 Mar 2022 16:44:07 GMT

Hello all,

I am trying to get the same 'share_outstanding' for each 'firm_id', 'year' group,

based on following table1

data table1;
infile cards dsd dlm=",";
input
firm_id :$15.
year :8.
fundno :$15.
shares :8.
share_outstanding :8.
;
cards;
36110,2002, 14086, 18000,  32000000
36110,2002, 15056, 92000,  32000000
36110,2002, 15058, 4740,   32000000
36110,2002, 16500, 35300,  27000000
36111,2002, 1903,  26700,  31000000
36111,2002, 1909,  543350, 31000000
36111,2002, 19658, 60000,  30000000
36111,2002, 2021,  20000,  30000000
36111,2002, 2034,  59700,  31000000
;;;;
run;
I expect to get
36110,2002, 14086, 18000,  32000000
36110,2002, 15056, 92000,  32000000
36110,2002, 15058, 4740,   32000000
36110,2002, 16500, 35300, 32000000
36111,2002, 1903,  26700,  31000000
36111,2002, 1909,  543350, 31000000
36111,2002, 19658, 60000,  31000000
36111,2002, 2021,  20000,  31000000
36111,2002, 2034,  59700,  31000000
by using first four lines as an example,

in this group (i.e., same 'firm_id' and 'year', as firm_id=36110 and year=2002), share_outstaning has three '32000000' and one '27000000'. and then I expect to replace '27000000' by '32000000' (i.e., set share_outsanding=32000000 when firm_id=36110 and year=2002), it is because I have more '32000000' than '27000000' in this group (firm_id='36110' , year=2002).

if more than one value is tied highest in the group then please keep them together.

for example,
"What if there are two of a certain value and two of another value?"
--please keep them both, because both of them are the 1/2 (highest) in this group.
"What if all four are different?'
--please keep all of them, because both of them are the 1/4 (highest) in this group.
could you please give me some advice about this .
thanks in advance.

Re: replace lower percentage of row by the greater percetange of row in each group

PaigeMiller — Tue, 01 Mar 2022 15:10:03 GMT

What rules do you follow here, when the share_outstanding is not the same in each firm_id,year group? In the cases you provide, there is three of one value and one different value. What if there are two of a certain value and two of another value? What if all four are different?

The explanation in your text is different than the explanation in your Subject line.

Please clarify.

Re: replace lower percentage of row by the greater percetange of row in each group

ballardw — Tue, 01 Mar 2022 16:16:34 GMT

Percentage of what???????

Your subject line makes no sense in terms of the data or example.

Re: replace lower percentage of row by the greater percetange of row in each group

Alexxxxxxx — Tue, 01 Mar 2022 16:22:58 GMT

Hi, @PaigeMiller , many thanks for your reply.

in most case, share_outstanding is the same in each firm_id, year group.

I expect to remain the highest percentage of value in each group, and if more than one value is tied highest in the group then please keep them together.

"What if there are two of a certain value and two of another value?"

--please keep them both, because both of them are the 1/2 (highest) in this group.

"What if all four are different?'

--please keep all of them, because both of them are the 1/4 (highest) in this group.

Re: replace lower percentage of row by the greater percetange of row in each group

PaigeMiller — Tue, 01 Mar 2022 16:34:23 GMT

Sure would be helpful if you could provide example data where these other situations actually occur.