BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LoisHNewMexico
Calcite | Level 5

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

4 REPLIES 4
Reeza
Super User

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;

dkb
Quartz | Level 8 dkb
Quartz | Level 8

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

Tom
Super User Tom
Super User

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

LoisHNewMexico
Calcite | Level 5

Thanks, everybody. SAS forum rocks!

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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