Help using Base SAS procedures

Next to last selection in datastep

Reply
Frequent Contributor
Posts: 131

Next to last selection in datastep

data test1;

set test;

by ln.no;

run;

sample output

ln_no         template          status_chng

100            wwe               12/15/12

111            abc                 1/12/13

111           cdd                 11/12/13

data test2;

set test1;

by ln_no;

if last.ln_no;

run;

sample output

ln_no         template          status_chng

111           cdd                 11/12/13

Is there a way to show a next to last transaction in a dataset.  I need to show the last.ln_no such as the dataset in test2 and another dataset for test3

data test3;

set test2;

by ln_no;

if ???????

run;

sample output

ln_no         template          status_chng

111            abc                 1/12/13

Is there a way to do a next to last transaction.  I know this is unusual

Respected Advisor
Posts: 4,646

Re: Next to last selection in datastep

To get the next to last in each ln_no group (when it exists) :

data test3;

set test2; by ln_no;

if last.no then

     if not first.ln_no then do;

          point = _n_ - 1;

          set test2 point=point;

          output;

          end;

run;

PG

PG
Super Contributor
Posts: 276

Re: Next to last selection in datastep

Hi,

Is this you are looking for ??

Data Test;

input name name1 $;

cards;

1 a

1 .

2 b

2 c

2 d

2 ee

3 e

3 f

4 g

4 h

;

run;

data test3;

set test;

by name;

a=first.name;

b=last.name;

run;

Data test4;

merge test3(drop=a b ) test3(firstobs=2 keep=b);

if b;

drop b;

run;

Thanks,

Sanjeev.K

N/A
Posts: 1

Re: Next to last selection in datastep

May this code help you...

data san;

input id name$;

id1=id;

cards;

10 a

10 b

10 c

10 d

20 e

20 f

20 g

20 h

30 i

30 j

30 k

30 l

;

run;

Proc sort data=san;

by id id1;

run;

data san1;

set san;

by id;

if (first.id=0)&&(last.id=0);

run;

data san2(keep=name id);

set san1;

by id1;

if last.id1;

run;

Thanks..

Super Contributor
Posts: 276

Re: Next to last selection in datastep

Hi,

Hope you found relevant answers for your question.

But one question going on my mind.On what basis you want to pick the"next to last observation" in that dataset.

above all solutions provides different outputs when you mention different variable in by group along with Ln_no.

For example if you use BY  LN_NO TEMPLATE ,then the output was different compared to BY LN_NO STATUS_CHNG.So Make sure that on what basic/logic you are extracting that "next to last observation".

If your requirement is to pick the nth observation from each BY group value,then you can use LAG function to accomplish that in a meaningful way.

Thanks

Sanjeev.K

Ask a Question
Discussion stats
  • 4 replies
  • 220 views
  • 3 likes
  • 4 in conversation