hello. i want to calculate one patient's 'maximum visit' of clinic, which changes over time. here is the raw data. it's medical billing data. 'id 1' visited 3 different clinic and 'id 2' visit 2 different clinic. in '20030124'(third row) 'id 1''s most visiting clinic is '12135' and maximum value is '2'. however in '20030607'(6th row), it is '75423' and maximum value is '3'. and in '20030815'(7th row) most visitng clinic is '12135' and '75423'. maximum value is stlll '3'. 'id 2''s most visiting clinic is '25875', and maximum value changes over time. it is 1, 2, 2, 3, 4, 5 in order. id date clinic 1 20020408 12135 1 20020715 12135 1 20030124 15109 1 20030308 75423 1 20030505 75423 1 20030607 75423 1 20030815 12135 2 20020615 25875 2 20020717 25875 2 20020824 55415 2 20020907 25875 2 20021215 25875 2 20030108 25875 and this is what i want. id date clinic count max 1 20020408 12135 1 1 1 20020715 12135 2 2 1 20030124 15109 1 2 1 20030308 75423 1 2 1 20030505 75423 2 2 1 20030607 75423 3 3 1 20030815 12135 3 3 2 20020615 25875 1 1 2 20020717 25875 2 2 2 20020824 55415 1 2 2 20020907 25875 3 3 2 20021215 25875 4 4 2 20030108 25875 5 5 (bold number is maximum value) *in addition, because it is national billing data, type of clinic is more than 10 thousands. so it is impossible to make virtual array by clinic. SAS can't comprehend that much column. i really appreciate your help.
... View more