CODEWITHSUNDEEP
sas
Deimont
Deimont

Reputation: 15

Sas base: one-to-one reading by biggest table or getting data from next row

Im new in sas base and need help. I have 2 tables with different data and I need merge it. But on step I need data from next row. example what I need:

ID  Fdate  Tdate  NFdate  NTdate

id1 date1  date1  date2   date2

id2 date2  date2  date3   date3

....

I did it by 2 merges:

data result;
 merge table1 table2 by ...; 
 merge table1(firstobs=2) table2(firstobs=2) by...;
run;

I expected 10 rows but got 9 becouse one-to-one reading stopted on last row of smallest table(merge). How I can get the last row (do one-to-one reading by biggest table)?

Upvotes: 1

Views: 76

Answers (1)

Tom
Tom

Reputation: 51621

Most simple data steps stop not at the bottom of the step but in the middle when they read past the end of the input. The reason you are getting N-1 observations is because the second input has one fewer records. So you need to do something to stop that.

One simple way is to not execute the second read when you are processing the last observation read by the first one. You can use the END= option to create a boolean variable that will let you know when that happens.

Here is simple example using SASHELP.CLASS.

data test;
  set sashelp.class end=eof;
  if not eof then set sashelp.class(firstobs=2 keep=name rename=(name=next_name));
  else call missing(next_name);
run;

Results:

                                                    next_
Obs    Name       Sex    Age    Height    Weight    name

  1    Alfred      M      14     69.0      112.5    Alice
  2    Alice       F      13     56.5       84.0    Barbara
  3    Barbara     F      13     65.3       98.0    Carol
  4    Carol       F      14     62.8      102.5    Henry
  5    Henry       M      14     63.5      102.5    James
  6    James       M      12     57.3       83.0    Jane
  7    Jane        F      12     59.8       84.5    Janet
  8    Janet       F      15     62.5      112.5    Jeffrey
  9    Jeffrey     M      13     62.5       84.0    John
 10    John        M      12     59.0       99.5    Joyce
 11    Joyce       F      11     51.3       50.5    Judy
 12    Judy        F      14     64.3       90.0    Louise
 13    Louise      F      12     56.3       77.0    Mary
 14    Mary        F      15     66.5      112.0    Philip
 15    Philip      M      16     72.0      150.0    Robert
 16    Robert      M      12     64.8      128.0    Ronald
 17    Ronald      M      15     67.0      133.0    Thomas
 18    Thomas      M      11     57.5       85.0    William
 19    William     M      15     66.5      112.0

Upvotes: 2

Related Questions