multiple table extract last obs

[BrahmanandaRao]

Hi  guys 

I have three tables in these three tables suppose i do not know how many observations in each table so

i want to find total observations and extract last five observations from each table at a time how can i write code

[Kurt_Bremser]

Retrieve the number of observations (nobs) from dictionary.tables, subtract 4, and use the result in a firstobs= option.

[BrahmanandaRao]

proc sql ;
select sum(nobs) as total_obs from dictionary.tables 
where libname = 'WORK' and memtype = 'data';
select memname, nobs from dictionary.tables 
where libname = 'WORK';
quit;

In the above code where subtract 4 

please explain

[Reeza]

That would give you the number of total observations. SAS allows you to access data sets using the FIRSTOBS, so if you take the Sum(nobs), you didn't give it a name, and subtract four that's the first observation you want.

ie

data want;
set sashelp.class (firstobs = 15);
run;

I would recommend Tom's solution since it's the simplest and you can generalize it for your multiple tables as needed. Either way, when processing multiple tables you'll need a macro or loop of some kind to get that working correctly.

[jhammouda]

You could also use proc SQL, substitute the name of your table in myBigtable.  The code should work to get you the last 5 observations of the table.

proc sql;
create table 
Last_Five as
  select *
    from myBigTable (firstobs=%eval(%sysfunc(attrn(%sysfunc(open(MyBigTable)),nlobs))-4))
  ;

quit;

[BrahmanandaRao]

Thanks for your reply i want last n observations from all datasets in a library how can we do that

[Tom]

---

@BrahmanandaRao wrote:
Thanks for your reply i want last n observations from all datasets in a library how can we do that

---

What do you want to DO with the last N observations from these datasets?  Do the datasets all have the exact same structure?  If so you might be able to put the results into a single dataset. Otherwise you will need to either make new datasets with jsut the last N observations or just make some type of report.  So what it is that you want?

[japelin]

%Macro GetLast5(lib,n);
  options dlcreatedir;
  libname out "%sysfunc(pathname(work))\out";
  data _null_;
    set sashelp.vtable(where=(memtype='DATA' and libname=upcase("&lib")));
    call symputx(cats('DS',_n_),memname);
         if nlobs=0   then call symputx(cats('obs',_n_),1);
    else if nlobs=>&n then call symputx(cats('obs',_n_),nlobs-(&n-1));
    else                   call symputx(cats('obs',_n_),nlobs);
    call symputx('nobs',_n_);
  run;

  %do i=1 %to &nobs;
    data out.&&DS&i;
      set &lib..&&DS&i(firstobs=&&obs&i);
    run;
  %end;
%Mend;

/* try extract last 5 obs from sashelp library */
%GetLast5(sashelp,5);

[Tom]

Use the POINT= and NOBS= option.  (Note requires that table does not contain deleted observations since POINT= operates on actual observation number and not logical observation number).

data want;
  do p=max(1,nobs-4) to nobs ;
    set have point=p nobs=nobs;
    output;
  end;
  stop;
run;