topic Re: last n distinct obs in SAS Programming

last n distinct obs

deleted_user — Wed, 10 Jun 2009 08:53:37 GMT

Hi friends,
what is most efficient way to find last 10 distinct obs from a data set.

we dont have such facility in SAS proc sql
like ---
select last 10 distinct abc, xyz from tbl where xyz='ijk';

Thank you.

regards,
Avi

Re: last n distinct obs

sbb — Wed, 10 Jun 2009 11:46:54 GMT

Look at following SAS programming opportunity:

1) capture SAS-reserved variable _N_ as OBSNUM for highest location identification.
2) use PROC SUMMARY to generate a _FREQ_ count. Specify ID OBSNUM to get the last occurence.
3) use SORT with BY DESCENDING _FREQ_ DESCENDING OBSNUM.

Scott Barry
SBBWorks, Inc.

Re: last n distinct obs

deleted_user — Wed, 10 Jun 2009 11:58:46 GMT

Hi,

To find last n observation from a dataset

data temp;
set sashelp.class nobs=LASTN;
if _n_ > (LASTN-n);
run;

This will get you the last n obs.

If you want to get distinct obseravtions,
Please create a dataset with distinct obseravtion first and apply this logic

Hope this will help you.

Re: last n distinct obs

deleted_user — Wed, 10 Jun 2009 12:47:26 GMT

Hey

Thanks for the reply.

Regards
Avi

Re: last n distinct obs

ChrisNZ — Wed, 10 Jun 2009 23:11:01 GMT

If the table is too large to process (sort, dedup, summarise) as suggested above,
you can read it starting from the end:
[pre]
data t;
if 0 then set SASHELP.CLASS nobs=NOBS;
do I= NOBS to 1 by -1;
set SASHELP.CLASS point=I;
put AGE=;
end;
stop;
run;[/pre]
You can add some logic in the data step to do what you want, like filling up an array with unique values as you go.

Re: last n distinct obs

BPD — Thu, 11 Jun 2009 07:38:16 GMT

A slight variation on the post from Chris@newzealand.
Store the number of obs - 9 into a macro variable.

data t;
if 0 then set SASHELP.CLASS nobs=NOBS;
call symput('nobs',nobs-9);
run;

Then read the data from that point in the dataset.
data t1;
set SASHELP.CLASS(firstobs=&nobs);
run;

Regards,

BPD

Re: last n distinct obs

deleted_user — Thu, 11 Jun 2009 08:26:45 GMT

Hey , Thanks Chris

I have got the required output...
Thank you all for your help..

Regards
Avi