topic assign last obs using macro in SAS Programming

assign last obs using macro

jojozheng — Wed, 24 Oct 2018 15:44:34 GMT

Hi,

I have a dataset like this

n date

1 31AUG2018

2 30JUN2018
3 31MAR2018
4 31DEC2017
5 30SEP2017
6 30JUN2017
7 31MAR2017

proc sql noprint;/*assign macro variable as_of_date*/
select catx('', quote(put(date, date9.)), 'd'),catx('', quote(put(date, date9.)), 'd'),n
into :dates1-,
:as_of_date separated by ',',
:n1-
from z;
%let n=&n;
%let as_of_date=(&as_of_date);
%let dates=&dates;

I want to assign last obs n=7, date=31MAR2017. since last n will change, it's not a fix number, it can be any number, this example gives last n=7. So how can I assign last n return to corresponding date??

Thanks!

Re: assign last obs using macro

mkeintz — Wed, 24 Oct 2018 16:29:58 GMT

I believe you simply need to retrieve the macrovar &SQLOBS, produced by the proc sql.

I.e. simply use

%let n=&sqlobs;

Re: assign last obs using macro

Reeza — Wed, 24 Oct 2018 16:35:29 GMT

Or do it in a data step which is trivial. Use either END if you want the last observeration only or use LAST. if you need the last by each group.

data _null_;
set sashelp.stocks (where=(stock='IBM')) end=eof;
if eof then 
call symputx('last_date', put(date, date9.));
run;

%put &last_date.;

Note in the sashelp.stocks data set the dates are in reverse order, so the last date on record is actually the earliest date.

@jojozheng wrote:

Hi,

I have a dataset like this

n date

1 31AUG2018

2 30JUN2018
3 31MAR2018
4 31DEC2017
5 30SEP2017
6 30JUN2017
7 31MAR2017

proc sql noprint;/*assign macro variable as_of_date*/
select catx('', quote(put(date, date9.)), 'd'),catx('', quote(put(date, date9.)), 'd'),n
into :dates1-,
:as_of_date separated by ',',
:n1-
from z;
%let n=&n;
%let as_of_date=(&as_of_date);
%let dates=&dates;

I want to assign last obs n=7, date=31MAR2017. since last n will change, it's not a fix number, it can be any number, this example gives last n=7. So how can I assign last n return to corresponding date??

Thanks!

Re: assign last obs using macro

DBailey — Wed, 24 Oct 2018 16:41:00 GMT

You could separate the n/as of date from the other date query as

select n, date
into :n, :as_of_date
from z
where n = (select max(n) from z);

Re: assign last obs using macro

Reeza — Wed, 24 Oct 2018 17:16:11 GMT

data have;
informat ID 8. date date9.;
input ID date;
cards;
1 31AUG2018 
2 30JUN2018
3 31MAR2018
4 31DEC2017
5 30SEP2017
6 30JUN2017
7 31MAR2017
;
run;

data want;
set have end=eof;
call symputx(catt('dates', put(_n_, 8. -l)), catt(quote(put(date, date9.)), 'd'));
if eof then do;
call symputx('n', put(_n_, 8. -l));
call symputx('as_of_date', catt(quote(put(date, date9.)), 'd'));
end;
run;

%put &dates1.;
%put &dates7.;
%put &n.;
%put &as_of_date.;

Full data step approach

@jojozheng wrote:

Hi,

I have a dataset like this

n date

1 31AUG2018

2 30JUN2018
3 31MAR2018
4 31DEC2017
5 30SEP2017
6 30JUN2017
7 31MAR2017

proc sql noprint;/*assign macro variable as_of_date*/
select catx('', quote(put(date, date9.)), 'd'),catx('', quote(put(date, date9.)), 'd'),n
into :dates1-,
:as_of_date separated by ',',
:n1-
from z;
%let n=&n;
%let as_of_date=(&as_of_date);
%let dates=&dates;

I want to assign last obs n=7, date=31MAR2017. since last n will change, it's not a fix number, it can be any number, this example gives last n=7. So how can I assign last n return to corresponding date??

Thanks!

Re: assign last obs using macro

data_null__ — Wed, 24 Oct 2018 17:50:15 GMT

If you just need to access the last obs you can point to it directly.

34   data _null_;
35      set have point=nobs nobs=nobs;
36      put _all_;
37      stop;
38      run;

nobs=7 ID=7 date=31MAR2017 _ERROR_=0 _N_=1

Deleted records disclaimer given.

Re: assign last obs using macro

Reeza — Wed, 24 Oct 2018 17:56:25 GMT

Agreed, except OP also wants a macro variable for each obs.

Re: assign last obs using macro

s_lassen — Fri, 26 Oct 2018 09:41:37 GMT

I am not quite sure that I understand your requirements, but from your code it looks like you are trying to do something like this:

proc sql noprint;
  select n,date into :n1-:n999,:date1-:date999
  from have
  order by n;
quit;

%let last=&sqlobs;
%let last_n=&&n&last;
%let as_of_date=&&date&last;

Re: assign last obs using macro

jojozheng — Mon, 29 Oct 2018 14:59:40 GMT

thank you ! it works!