Hello,
I am having an issue with a research paper I am into. I have created a table with proc sql, which displays several variables, among which price and date. How can I keep only the rows for which the price value at t-31 (therefore 31 days before the value reported in the date column) is greater than 5?
I need something like prc(t-31)>5.
I know this may sound trivial, but I am really stuck in this issue.
Thanks
Riccardo
You can merge the data set with itself (31 days earlier) in PROC SQL.
Here's an example using SAS dataset SASHELP.CITYDAY
proc sql;
create table want as select a.*
from sashelp.citiday as a left join sashelp.citiday as b
on b.date=(a.date-31) where b.dtbd3m>6;
quit;
You can merge the data set with itself (31 days earlier) in PROC SQL.
Here's an example using SAS dataset SASHELP.CITYDAY
proc sql;
create table want as select a.*
from sashelp.citiday as a left join sashelp.citiday as b
on b.date=(a.date-31) where b.dtbd3m>6;
quit;
Hello,
the post of @PaigeMiller has given you the right response. I've also seen that you liked it. It's even better to mark it as a solution!
With my post I just want to make you aware that there exists a procedure in SAS/ETS that directly supports your [t-31] logic. It's PROC TIMEDATA. Have a look at the program below.
PROC TIMEDATA data=sashelp.citiday out=_NULL_ /*PRINT=(ARRAYS)*/ OUTARRAY=wanted1;
outarrays dtbd3m_31 my_dtbd3m;
/*by by_var*/;
id date interval=day accumulate=total format=date9.;
var dtbd3m;
do t = 32 to dim(dtbd3m);
dtbd3m_31[t] = dtbd3m[t-31];
if dtbd3m_31[t]>6 then my_dtbd3m[t]=dtbd3m[t]; else my_dtbd3m[t]=-99999;
end;
run; QUIT;
data wanted1;
set wanted1;
if _N_<32 then delete;
if my_dtbd3m=-99999 then delete;
run;
PROC SQL noprint;
create table wanted2 as
select *
from wanted1
where date IN (select date from sashelp.citiday);
QUIT;
I get the same 411 rows in return as @PaigeMiller of course.
Cheers,
Koen
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!
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.