topic Re: Problem to Organize a Table in Statistical Procedures

Problem to Organize a Table

RDellaVilla — Tue, 23 Mar 2021 16:05:04 GMT

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

Re: Problem to Organize a Table

PaigeMiller — Tue, 23 Mar 2021 16:13:15 GMT

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;

Re: Problem to Organize a Table

sbxkoenk — Tue, 23 Mar 2021 18:39:38 GMT

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