SAS changing variable value when I merge?

LoisHNewMexico — Wed, 04 Feb 2015 01:25:55 GMT

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;

Re: SAS changing variable value when I merge?

Reeza — Wed, 04 Feb 2015 02:52:22 GMT

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;

Re: SAS changing variable value when I merge?

dkb — Wed, 04 Feb 2015 03:02:39 GMT

What's the length of the count variable on the frame table?

Re: SAS changing variable value when I merge?

Tom — Wed, 04 Feb 2015 03:08:01 GMT

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

Re: SAS changing variable value when I merge?

LoisHNewMexico — Wed, 04 Feb 2015 17:09:28 GMT

Thanks, everybody. SAS forum rocks!

topic Re: SAS changing variable value when I merge? in SAS Procedures

SAS changing variable value when I merge?

Re: SAS changing variable value when I merge?

Re: SAS changing variable value when I merge?

Re: SAS changing variable value when I merge?

Re: SAS changing variable value when I merge?