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;
Because the LENGTH of the variable in the first data set is too small to store such a large number exactly.
data x ;
length n3 3 n4 4 n5 5 n6 6 n7 7 n8 8 ;
array _n n3-n8 ;
do over _n; _n = 26242; end;
run;
data _null_;
set x;
put (_all_) (=/);
run;
n3=26240
n4=26242
n5=26242
n6=26242
n7=26242
n8=26242
Why in your second step isn't the second table the tmp table?
data new;
merge frame tmp;
by fipscode;
run;
data new2;
update frame (in=a)
records (in=b);
by fipscode;
run;
What's the length of the count variable on the frame table?
Because the LENGTH of the variable in the first data set is too small to store such a large number exactly.
data x ;
length n3 3 n4 4 n5 5 n6 6 n7 7 n8 8 ;
array _n n3-n8 ;
do over _n; _n = 26242; end;
run;
data _null_;
set x;
put (_all_) (=/);
run;
n3=26240
n4=26242
n5=26242
n6=26242
n7=26242
n8=26242
Thanks, everybody. SAS forum rocks!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: