topic Re: How to Identity 2nd Last Record in SAS Studio

How to Identity 2nd Last Record

Kraj1989 — Fri, 17 May 2019 12:40:46 GMT

Data SASdat.Emp;
input EMP_ID Name $ Salary Sal_Date date9.;
format Sal_Date date9.;
datalines;
101 A 10000 31-May-2016
102 B 15000 30-Jun-2016
103 C 20000 31-Jul-2016
104 D 18000 31-Aug-2016
105 E 13000 30-Sep-2016
106 F 80000 31-Oct-2016
107 G 30000 30-Nov-2016
108 H 35000 31-Dec-2016
;

I need only Emp_Id with 107

Re: How to Identity 2nd Last Record

novinosrin — Fri, 17 May 2019 13:04:27 GMT

Hi @Kraj1989

Just a direct access

Data Emp;
input EMP_ID Name $ Salary Sal_Date date9.;
format Sal_Date date9.;
datalines;
101 A 10000 31-May-2016
102 B 15000 30-Jun-2016
103 C 20000 31-Jul-2016
104 D 18000 31-Aug-2016
105 E 13000 30-Sep-2016
106 F 80000 31-Oct-2016
107 G 30000 30-Nov-2016
108 H 35000 31-Dec-2016
;

 
data want;
i=nobs-1;
set emp nobs=nobs point=i;
output;
stop;
run;

Re: How to Identity 2nd Last Record

Jagadishkatam — Fri, 17 May 2019 14:27:47 GMT

Alternatively,

data want;
set emp nobs=nobs ;
if _n_=nobs-1;
run;

Re: How to Identity 2nd Last Record

ballardw — Fri, 17 May 2019 14:40:35 GMT

@Kraj1989 wrote:

Data SASdat.Emp;
input EMP_ID Name $ Salary Sal_Date date9.;
format Sal_Date date9.;
datalines;
101 A 10000 31-May-2016
102 B 15000 30-Jun-2016
103 C 20000 31-Jul-2016
104 D 18000 31-Aug-2016
105 E 13000 30-Sep-2016
106 F 80000 31-Oct-2016
107 G 30000 30-Nov-2016
108 H 35000 31-Dec-2016
;

I need only Emp_Id with 107

Here's a silly question: Do you actually need Emp_id = 107 regardless of sort order or the 2nd to last record regardless of actual values stored in that record?

The content of values is a much more common question because data order can change.

If the Emp_id is what you want then

data want;
   set sasdat.emp;
   where Emp_id=107;
run;

Re: How to Identity 2nd Last Record

JackHamilton — Fri, 17 May 2019 17:35:37 GMT

As ballardw notes, the question of whether you want the next to last record is physical order or in sorted order is an important one.

The approach using NOBS= is potentially dangerous. It works only for native SAS data sets (or data step views) that have not been edited. It potentially does not work for data sets in a database, or transport data sets, or data sets that have been edited.

Using the data above

83 data _null_;

84 i = nobs-1;

85 set emp nobs=nobs point=i;

86 put (_all_)(=);

87 stop;

88 run;

EMP_ID=107 Name=G Salary=30000 Sal_Date=30NOV2020

but

90 proc sql;

91 delete from emp

92 where emp_id = 107;

NOTE: 1 row was deleted from WORK.EMP.

93 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

95 data _null_;

96 i = nobs-1;

97 set emp nobs=nobs point=i;

98 put (_all_)(=);

99 stop;

100 run;

EMP_ID=. Name= Salary=. Sal_Date=.

i=7 nobs=8 EMP_ID=. Name= Salary=. Sal_Date=. _ERROR_=1 _N_=1

This problem is a variation on calculating lags and leads. See this 2019 SAS Global Forum paper for some ideas:

https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2019/3699-2019.pdf

Re: How to Identity 2nd Last Record

novinosrin — Fri, 17 May 2019 18:33:32 GMT

Data Emp;
input EMP_ID Name $ Salary Sal_Date : date9.;
format Sal_Date date9.;
datalines;
101 A 10000 31-May-2016
102 B 15000 30-Jun-2016
103 C 20000 31-Jul-2016
104 D 18000 31-Aug-2016
105 E 13000 30-Sep-2016
106 F 80000 31-Oct-2016
107 G 30000 30-Nov-2016
108 H 35000 31-Dec-2016
;

proc sql;
create table want as
select *
from emp
having monotonic()=count(*)-1;
quit;

Re: How to Identity 2nd Last Record

novinosrin — Fri, 17 May 2019 18:44:40 GMT

Hello @Kraj1989 Something I noticed just now which I should have earlier is that your date values are sorted and denote the last day of the month. If my observation is correct, a simple approach like the following is convenient

Data Emp;
input EMP_ID Name $ Salary Sal_Date : date9.;
format Sal_Date date9.;
datalines;
101 A 10000 31-May-2016
102 B 15000 30-Jun-2016
103 C 20000 31-Jul-2016
104 D 18000 31-Aug-2016
105 E 13000 30-Sep-2016
106 F 80000 31-Oct-2016
107 G 30000 30-Nov-2016
108 H 35000 31-Dec-2016
;

proc sql;
create table want as
select *
from emp
having Sal_Date=intnx('month',max(Sal_Date),-1,'e');
quit;

Re: How to Identity 2nd Last Record

JackHamilton — Fri, 17 May 2019 19:43:45 GMT

The monotonic function can be useful, but it is not documented, and is not guaranteed to return sequential numbers with no gaps starting from 1. That's not promised by the definition of monotonic: "varying in such a way that it either never decreases or never increases".
In his paper on adding sequence numbers, http://support.sas.com/resources/papers/proceedings14/1277-2014.pdf , Howard Schreier says:
"The catch is that the MONOTONIC function is not documented. Organizations may have policies prohibiting the use of undocumented features. Even in the absence of such a policy, a coder may be reluctant to rely on the function. In developing examples to illustrate the usage of the MONOTONIC function, the author encountered some surprising and disconcerting results. In some cases, series returned by MONOTONIC began not with 1 (one), but rather with higher integers. Unfortunately, it seemed not possible to produce such results consistently, or to experimentally determine just what circumstances cause the surprising and suspicious offsets."

Re: How to Identity 2nd Last Record

novinosrin — Sat, 18 May 2019 02:18:29 GMT

Interesting feedback.

So no NOBS= or MONOTONIC()


Data Emp;
input EMP_ID Name $ Salary Sal_Date : date9.;
format Sal_Date date9.;
datalines;
101 A 10000 31-May-2016
102 B 15000 30-Jun-2016
103 C 20000 31-Jul-2016
104 D 18000 31-Aug-2016
105 E 13000 30-Sep-2016
106 F 80000 31-Oct-2016
107 G 30000 30-Nov-2016
108 H 35000 31-Dec-2016
;

data want;
set emp end=lr;
n+1;
if lr;
n=n-1;
set emp point=n;
output;
run;

Re: How to Identity 2nd Last Record

JackHamilton — Sat, 18 May 2019 03:08:45 GMT

NOBS= is OK if you just created the data set yourself in the same program, so you know it hasn't been edited.
If you've set POINTOBS=NO, you can use NOBS= but not POINT=. REUSE=YES causes POINTOBS to be set to know. You might want to use POINTOBS=NO because, according to the documentation, writing a compressed data set with POINTOBS=YES increases CPU usage by 10% when creating a compressed data set.
There's a function, ATTRN, which gives more information about the number of observations in a data set, and potentially overcomes the problem of deleted observations. https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=p12uchp7hm5h2zn1om2ut816af7h.htm&docsetVersion=9.4&locale=en

Re: How to Identity 2nd Last Record

novinosrin — Sat, 18 May 2019 03:27:36 GMT

Yes aware of attrn, but how about

data want ;
if _n_=1 then do;
   dcl hash H () ;
   h.definekey  ("_n_") ;
   h.definedata ("EMP_ID"," Name"," Salary"," Sal_Date ") ;
   h.definedone () ;
end;
set emp end=lr;
h.add();
if lr ;
_n_=_n_-1;
_iorc_=h.find()	;
run;

Re: How to Identity 2nd Last Record

Tom — Sat, 18 May 2019 04:19:07 GMT

Only need the END= and FIRSTOBS= options to work. That should work for any dataset, including sequential access datasets (like XPORT files, views or what SAS used to call TAPE datasets).

data want ;
  set emp;
  set emp (firstobs=2 drop=_all_) end=eof;
  if eof ;
run;

So on the next to last observation the EOF will be true. On the last observation the second SET will read past the end and the data step will stop before the IF statement is reached.

If there are less than 2 observations then none will be written.

Same method can be used to find the 3rd to last, 4th to last etc by just changing the value of the FIRSTOBS= option.

Re: How to Identity 2nd Last Record

JackHamilton — Mon, 20 May 2019 18:52:19 GMT

I hadn't though of using DROP=_ALL_. That's a very clean and extensible solution.