Check if Colum total of a variable to greater than another variable for a unique plotID

[andrew roder]

Dear All,

Can you help me with the following:


Generate the column total of MortgagedArea by PlotID and total of AvailableArea by PlotId. once the total is generated i want to filter all those cases where for the same PlotID the total mor area is greater than the total available area for the same PlotID. An example of my dataset :

As you can see the same plotID will be in the different row (long type format)

copy starting from the next line ------- ---------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str20 PlotId double(PlotArea AvailableArea MortgagedArea)
"0.343"              .343    .343    .343
"106"                3905    3905    3905
"11U-108"           10228       0   10228
"11U-124"           14371       .   14371
"11U-128"            7207       0    1437
"11U-128"            7207       .    1473
"11U-145"           10022       .   10022
"11U-147"            2792       .    2792
"11U-157"           64547       .   64547
"11U-159"           46947   46947   46947
"11U-165"            5273    5273    5273
"11U-168"            4356       0    4356
"11U-17"             3165       0    3165
"11U-171"            5932       0    5932
"11U-172"           13070   13070   13070
"11U-172"           13070       .   13070
"11U-173"            8720       0    8712
"11U-189,11U-190"   21181   21181   21181
"11U-19"             4460       .    4460
"11U-195"           10889       0   10889
"11U-213"           11339   11339   11339
"11U-216"         9601.12 9601.12  9601.1
"11U-22"             2905       0    2905
"11U-237"            6533    6533    6533
"11U-238"            3471       0    3471
"11U-24"            32244       0   32244
"11U-242"           16107   16107   16107
"11U-246"            6554       0    6554
"11U-248"            5647       .    5647
"11U-262"         5360.84       0 5360.84
"11U-273"            5696       .    5696
"11U-277"            3278       0    3013
"11U-278"            3012       0    3012
"11U-280"            3013       0    3013
"11U-288"            3008       0    3008
"11U-301"            3042    3042    3042
"11U-302"            2952    2952    2952
"11U-375"            4607       .    4607
"11U-390"            5655       .    5655
"11U-391"            3020       .    3020
"11U-392"            1790       .       0
"11U-410"            8694       .    8694
"11U-449"            8967       0    8967
"11U-6"              4330       .    4330
"11U-73"             5880       0    5880
"11U-84"             8060       0    8060
"11U-85"            10074       0   10074
"11u-127"           11447       0 11325.6
"128"               10454   10454   10454
"134"               16308   16308   16308
"135"                7950    7950    7950
"13U-2233"           1498       .    1498
"13U-2239"           1620       0    1620
"13U-2244"           1499       0    1499
"13U-2245"           1501       .    1501
"13U-2246"           1482       0    1482
"14U-10"             3278       .    3278
"14U-119"           10454   10454   10454
"14U-12"             2703       0    2703
"14U-131"            4406       .    4406
"14U-134"            6912       .    6912
"14U-135"            6621       0    6621
"14U-138"           13803       .   13803
"14U-14"             3158       0    3158
"14U-146"            4714       0    4356
"14U-148"            4366       0    4366
"14U-15"             1164       0    1164
"14U-151"            4245       0    4245
"14U-152"            4739       .    4739
"14U-158"            5291       0    5291
"14U-171"           11704   11704   11704
"14U-173"            4600       .    4600
"14U-174"           15885       .   15885
"14U-184"            4393       .    4393
"14U-188"            5581       0    5581
"14U-189"            7035       0    7035
"14U-192"            9976       0    9976
"14U-193"            9351       0    9351
"14U-203"           57684       0   31186
"14U-208"           24298       .   24298
"14U-22"             1647       .    1647
"14U-23"             2919       0    2919
"14U-23"             2919       .    2919
"14U-25"             1239       0    1239
"14U-266"            3189       0    3189
"14U-268"            7640       .    7640
"14U-274"            3059       .    3059
"14U-279"            3282    3282    3282
"14U-280"            3188       0    3188
"14U-283"            4754       0    4225
"14U-286"            5450       .    5450
"14U-290"            5219       0    5219
"14U-308"            5335       .    5335
"14U-31"             2342       .    2342
"14U-32"             1600       0    1600
"14U-33"             1266    1266    1266
"14U-345"            3570       0    3570
"14U-354"            6832       0    6832
"14U-360"            1220       0    1220
"14U-361"             945       0     945
end

copy up to and including the previous line -- ---------------

Listed 100 out of 135586 observations
Use the count() option to list more

[Hemanshu Kumar]

egen total_MortgagedArea = total(MortgagedArea), by(PlotId)
egen total_AvailableArea = total(AvailableArea), by(PlotId)

gen wanted = (total_MortgagedArea > total_AvailableArea)
egen tagged = tag(PlotId) if wanted

list PlotId total_MortgagedArea total_AvailableArea if tagged, noobs

[andrew roder]

THanks @Hemanshu but it is not able to check by the same plotID. The condition is that the plot ID should be same

[Hemanshu Kumar]

andrew roder in fact, it does. This is because the comparisons are being made across columns in the same row, and one row only has one PlotId.

If it doing something different from what you expect, can you pick out an example and show me what you find wrong?