topic Next to last selection in datastep in SAS Procedures

Next to last selection in datastep

Q1983 — Wed, 22 Jan 2014 00:52:54 GMT

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

Re: Next to last selection in datastep

PGStats — Wed, 22 Jan 2014 01:52:57 GMT

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;

Re: Next to last selection in datastep

kuridisanjeev — Wed, 22 Jan 2014 05:02:07 GMT

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

Re: Next to last selection in datastep

SantoshJoshi — Wed, 22 Jan 2014 07:06:03 GMT

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..

Re: Next to last selection in datastep

kuridisanjeev — Wed, 22 Jan 2014 08:24:10 GMT

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