topic Proc SQL equivalent for a data step in SAS Programming

Proc SQL equivalent for a data step

wbsjd — Thu, 26 May 2022 22:47:17 GMT

%let num=%sysfunc(intck(month,'01jan2022'd,%sysfunc(today())));

data b;
set a;
%macro bb;
%do n=0 %to &num;
%let beg=%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-&n)),date9.));
%let end=%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-&n,end)),date9.));
%let mon=%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-&n)),yymmd.));

 if begin_date le "&end"d and end_date ge "&beg"d 
        then do;
            mon="&mon"; 
	output;
	end; 
%end;
%mend;
%bb
run;

Hello Everyone,

Does anyone know the Proc SQL equivalent for the attached SAS Data Step? After running the attached the SAS Data Step code, data b would have multiple records correspond to one record in data a (when meets the condition, output a new record).

Thank you!

Re: Proc SQL equivalent for a data step

Tom — Thu, 26 May 2022 23:14:41 GMT

Yikes. That is a very confusing way to write a data step. Remember the macro processor (the macro pre-processor) finishes its work before the resulting text is passed onto SAS itself to interpret. So putting macro code, ,especially macro definitions, in the middle of a data step is just going to confuse the humans trying to read the code.

So let's first try to untangle that so we can see what type of data step you are trying to generate with that macro code. So I think you are trying to run something like this:

%macro bb(num);
%local n beg end mon;
%do n=0 %to &num;
  %let beg=%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-&n)),date9.));
  %let end=%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-&n,end)),date9.));
  %let mon=%sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-&n)),yymmd.));

if begin_date le "&end"d and end_date ge "&beg"d then do;
   mon="&mon"; 
   output;
end; 

%end;
%mend;

data b;
  set a;
  %bb(num=%sysfunc(intck(month,'01jan2022'd,%sysfunc(today()))))
run;

Which would be a lot easier without the macro code. Perhaps like this?

data b;
  set a;
  do offset=0 to intck('month',max(begin_date,'01JAN2022'd),min(end_date,today));
    mon = put(intnx('month',max(begin_date,'01JAN2022'd),offset),mmddd7.);
    output;
  end;
  drop offset;
run;

If you wanted to so something like that in SQL code then create the target months as a dataset and join the target months with the data.

data months;
  do offset=0 to intck('month','01JAN2022'd,today());
     begdt = intnx('month','01JAN2022'd,offset);
     enddt = intnx('month','01JAN2022'd,offset,'end');
     mon = put(begdt,yymmd7.);
    output;
  end;
run;
proc sql;
create table b as
  select a.*,b.mon
  from a inner join months b
  on a.begin_date le b.enddt and a.end_date ge b.begdt
;
quit;

Re: Proc SQL equivalent for a data step

wbsjd — Fri, 27 May 2022 00:06:48 GMT

Your solution is awesome, works perfectly, thank you so much!