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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.