I am using this code to merge two tables:
data new;
merge frame tmp;
by fipscode;
run;
The table, TMP, is the result of a proc summary. The value of count where fipscode =. is the total of records across all counties in the dataset. --------------------------------- 1. TMP - numerator dataset fipscode _TYPE_ _FREQ_ count . 0 26242 26242 1 1 8107 8107 3 1 21 21 5 1 936 936 6 1 361 361 7 1 136 136 9 1 914 914 11 1 15 15 13 1 3029 3029 15 1 882 882 17 1 310 310 19 1 41 41 21 1 5 5 23 1 47 47 25 1 1200 1200 27 1 184 184 28 1 158 158 29 1 389 389 31 1 1143 1143 33 1 44 44 35 1 830 830 37 1 78 78 39 1 561 561 41 1 302 302 43 1 1387 1387 45 1 1901 1901 47 1 315 315 49 1 1310 1310 51 1 115 115 53 1 220 220 55 1 288 288 57 1 147 147 59 1 36 36 61 1 830 830 The table, Data Frame, is a list of all counties with initial zero values for count. The two are joined so that all counties get a row in the table. There are not always records in the dataset for all counties, but I want all counties to appear in the final table. So all counties start with a value of zero, and if there are records for that county in the TMP table, the count value should replace the zero value. --------------------------------- 2. Data Frame count fipscode 0 . 0 1 0 3 0 5 0 6 0 7 0 9 0 11 0 13 0 15 0 17 0 19 0 21 0 23 0 25 0 27 0 28 0 29 0 31 0 33 0 35 0 37 0 39 0 41 0 43 0 45 0 47 0 49 0 51 0 53 0 55 0 57 0 59 0 61 0 98 0 99 My problem with result (below) is the state total. For some reason, the value for the count variable has changed from 26242 to 26240. Why? And more importantly, how do I get it to stop? --------------------------------- 3. NEW, after frame updated by records count fipscode _TYPE_ _FREQ_ 26240 . 0 26242 8107 1 1 8107 21 3 1 21 936 5 1 936 361 6 1 361 136 7 1 136 914 9 1 914 15 11 1 15 3029 13 1 3029 882 15 1 882 310 17 1 310 41 19 1 41 5 21 1 5 47 23 1 47 1200 25 1 1200 184 27 1 184 158 28 1 158 389 29 1 389 1143 31 1 1143 44 33 1 44 830 35 1 830 78 37 1 78 561 39 1 561 302 41 1 302 1387 43 1 1387 1901 45 1 1901 315 47 1 315 1310 49 1 1310 115 51 1 115 220 53 1 220 288 55 1 288 147 57 1 147 36 59 1 36 830 61 1 830 0 98 . . 0 99 . . Furthermore, the same thing happens when I use an update:
data new;
update frame (in=a)
records (in=b);
by fipscode;
run;
... View more