Help using Base SAS procedures

SAS changing variable value when I merge?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

SAS changing variable value when I merge?

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;


Accepted Solutions
Solution
‎02-03-2015 10:08 PM
Super User
Super User
Posts: 6,502

Re: SAS changing variable value when I merge?

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

View solution in original post


All Replies
Super User
Posts: 17,961

Re: SAS changing variable value when I merge?

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;

Contributor dkb
Contributor
Posts: 53

Re: SAS changing variable value when I merge?

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

Solution
‎02-03-2015 10:08 PM
Super User
Super User
Posts: 6,502

Re: SAS changing variable value when I merge?

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

Occasional Contributor
Posts: 7

Re: SAS changing variable value when I merge?

Thanks, everybody. SAS forum rocks!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 224 views
  • 3 likes
  • 4 in conversation