topic Re: last row of a dataset in All Things Community

last row of a dataset

monalisa — Wed, 19 Oct 2016 08:44:32 GMT

I have an existing SAS query the last row of a dataset is fetched on below condition:

If Last.variable1 OR Last.variable2

Can any one please help me in understanding the above condition?

Thanks

Re: last row of a dataset

Kurt_Bremser — Wed, 19 Oct 2016 08:51:05 GMT

You have by statement in that data step that contains both variable1 and variable2.

last.variable1 is an automatic boolean variable that is set to 1 when the next observation will have a different value for variable1 than the current observation, likewise for variable2

Re: last row of a dataset

RW9 — Wed, 19 Oct 2016 08:55:43 GMT

There are many topics out there on this subject including the SAS documentation. last.<by group variable> is a flag assigned to the last observeration within each by group. So imainge you by group this data:

A B

1 1

1 2

2 1

2 2

2 3

If you say:

data want;
  set have;
  by a b;

You data would look like this (note the additional variables are not stored in the output dataset only during processing):

A B lastflag<a> lastflag<b>

1 1 1

1 2 1 1

2 1 1

2 2

2 2 1

2 3 1 1

So the flag is set on the last occurence of the variable within the by group. So your logic:

A B lastflag<a> lastflag<b> if

1 1 1 =true

1 2 1 1 =true

2 1 1 =true

2 2 =false

2 2 1 =true

2 3 1 1 =true

Re: last row of a dataset

monalisa — Wed, 19 Oct 2016 08:59:31 GMT

Thanks.
Yes the query has order by variable1 , variable2.

Can you help me in understanding with one example

Re: last row of a dataset

Kurt_Bremser — Wed, 19 Oct 2016 09:19:54 GMT

This should illustrate the workings of the last. variables sufficiently:

data have;
input variable1 variable2;
cards;
1 1
1 1
1 2
1 3
2 3
2 4
3 1
;
run;

data want;
set have;
by variable1 variable2;
label
  x1='last.variable1'
  x2='last.variable2'
;
x1 = last.variable1;
x2 = last.variable2;
run;

proc print label;run;

Result:

                                   last.        last.
Obs    variable1    variable2    variable1    variable2

 1         1            1            0            0    
 2         1            1            0            1    
 3         1            2            0            1    
 4         1            3            1            1    
 5         2            3            0            1    
 6         2            4            1            1    
 7         3            1            1            1

Re: last row of a dataset

monalisa — Wed, 19 Oct 2016 09:42:08 GMT

what does OR operator signifies in the statement then.If Last.variable1 OR Last.variable2

Re: last row of a dataset

Kurt_Bremser — Wed, 19 Oct 2016 10:01:20 GMT

@monalisa wrote:
what does OR operator signifies in the statement then.If Last.variable1 OR Last.variable2

You know what a logical or is? If not, I guess you are in a completely wrong place here.

Re: last row of a dataset

RW9 — Wed, 19 Oct 2016 10:03:02 GMT

See logical boolean operation in the guidance:

https://v8doc.sas.com/sashtml/lrcon/z0780367.htm

Re: last row of a dataset

Astounding — Wed, 19 Oct 2016 13:29:59 GMT

One of the implications of "OR" in this code is that the person who wrote the code is unsure of what they are doing. Whoever wrote it may not be a good person to get help from in understanding the code.

When the program uses this statement:

by variable1 variable2;

One result is that this statement is redundant:

if last.variable1 or last.variable2;

The observations selected by the condition "last.variable2" include all the observations that would be selected by "last.variable1". The same results would be obtained by coding:

if last.variable2;

None of this is a substitute for reading the documentation and learning about BY variables. But it is a fact of life that you can inherit code written programmers who have varying degrees of expertise. One of the challenges is figuring out whether the author of the code made good choices when selecting programming tools.