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!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.