assign last obs using macro

[jojozheng]

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! 

[mkeintz · Accepted solution]

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

I.e. simply use

   %let n=&sqlobs;

[jojozheng]

thank you ! it works!

[Reeza]

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! 

---

[DBailey]

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

[Reeza]

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! 

---

[data_null__]

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.

[Reeza]

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

[s_lassen]

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;