Dear All, I have a large dataset stored on a remote SAS server with approximately 400 million rows and 88 columns. My goal is to create a new column by shifting a date variable by 1 month. I intend to do this with a simple left join, similar to what I would do in a SQL server. My problem is as follows: when I tried to use the ALTER TABLE command, it ran indefinitely without producing any results. However, when I created a new table, the process finished in 30 minutes. How should I properly execute this process? Here is the hard-copy version of my code: rsubmit;
%let start_time = %sysfunc(datetime());
proc sql;
drop table table_v2;
create table table_v2 as
select *,
t2.variable as shifted_variable
from table_v1 as t1
left join (select date, deal_id, variable from table_v1) as t2
on t1.deal_id = t2.deal_id and t1.date = intnx('month', t2.date, -1, 'E')
order by t1.deal_id, t1.date;
quit;
%let end_time = %sysfunc(datetime());
%let duration = %sysevalf(&end_time - &start_time);
%put NOTE: The query took &duration seconds to run.;
endrsubmit; Thank you for your assistance! Gabor
... View more
