topic Re: multiple table extract last obs in SAS Programming

multiple table extract last obs

BrahmanandaRao — Fri, 10 Jan 2020 17:39:33 GMT

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

Re: multiple table extract last obs

Kurt_Bremser — Fri, 10 Jan 2020 18:25:26 GMT

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

Re: multiple table extract last obs

jhammouda — Fri, 10 Jan 2020 18:39:54 GMT

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;

Re: multiple table extract last obs

Tom — Fri, 10 Jan 2020 19:05:10 GMT

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;

Re: multiple table extract last obs

BrahmanandaRao — Sat, 11 Jan 2020 14:55:38 GMT

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

Re: multiple table extract last obs

Reeza — Sun, 12 Jan 2020 01:39:24 GMT

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.

Re: multiple table extract last obs

BrahmanandaRao — Thu, 24 Dec 2020 04:47:22 GMT

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

Re: multiple table extract last obs

Tom — Thu, 24 Dec 2020 04:58:28 GMT

@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?

Re: multiple table extract last obs

japelin — Thu, 24 Dec 2020 06:38:56 GMT

%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);