Hi,
I have a dataset like this
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!
I believe you simply need to retrieve the macrovar &SQLOBS, produced by the proc sql.
I.e. simply use
%let n=&sqlobs;
I believe you simply need to retrieve the macrovar &SQLOBS, produced by the proc sql.
I.e. simply use
%let n=&sqlobs;
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 31AUG20182 30JUN2018
3 31MAR2018
4 31DEC2017
5 30SEP2017
6 30JUN2017
7 31MAR2017proc 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!
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);
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 31AUG20182 30JUN2018
3 31MAR2018
4 31DEC2017
5 30SEP2017
6 30JUN2017
7 31MAR2017proc 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!
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.