topic Re: loop in proc sql in SAS Programming

loop in proc sql

AmirSari — Sat, 04 Feb 2023 08:22:48 GMT

Hi everyone,

I am trying to run a proc sql for 100 datasets and was wondering if it is possible to use a do loop instead of writing the same code 100 times. here is my proc sql:

proc sql;
  create table new&i as
    select distinct a.*,b.date, b.date2, b.x, b.y, b.z 
      from v&i a, &data b
      where abs(intck('day',a.date,b.date))<=29
      group by a.id, a.date
        having abs(a.date2-b.date2) eq min(abs(a.date2-b.date2))
  ;
quit;

Any help is greatly appreciated!

Re: loop in proc sql

Kurt_Bremser — Sat, 04 Feb 2023 08:32:23 GMT

Use a macro loop:

%macro loop;
%do i = 1 %to 100;
create table new&i as
  select distinct a.*,b.date, b.date2, b.x, b.y, b.z 
  from v&i a, &data b
  where abs(a.date-b.date)<=29
  group by a.id, a.date
  having abs(a.date2-b.date2) eq min(abs(a.date2-b.date2))
;
%end;
%mend;
proc sql;
%loop
quit;

What keeps you from combining the 100 datasets into one in the first place?

Since dates are counts of days, you don't need the INTCK function. A simple subtraction will be faster.

Re: loop in proc sql

AmirSari — Sat, 04 Feb 2023 16:09:24 GMT

Thank you for the code! The dataset is huge and takes forever to run this code. so I split it into 100 smaller datasets.