New Ins and Outs- Automated(Loop)

[BaalaRaaji]

Hi there,

I have got 18 months data as a monthly report.

Every month i need to get the New Ins (Accounts in Current month but Not in Last month) and New Outs( Account is Last month but not in Current month)..

Below is the basic query i have written but its find of manual every month to run and save the data to the historic table . As am using this data separately in tableau to get the charts by months.

Below is my query..

/* Monthly report */

data Monthly_report;
set Final_analysisSEPT19;
where end_of_month >='30JUN2018'D ;
run;

DATA TEST;
format today date9.;
today=today();
call symputx ('last_month',put(intnx('month',today,-1,'e'),date9.)); /* 31OCT2019 */
call symputx ('lastp_month',put(intnx('month',today,-2,'e'),date9.)); /* 30SEP2019 */
RUN;

%put &last_month. &lastp_month.;

/* New ins and New Outs by each Monthly- monthly report */

data current;
set Monthly_report ;
where end_of_month = "&last_month."d;
run;

data last;
set Monthly_report ;
where end_of_month = "&lastp_month."d;
run;

proc sql;
create table New_in as
select * from current where accno not in (Select accno from last); quit;

proc sql;
create table New_out as
select * from last where accno not in (Select accno from current); quit;

data IMP.New_in_&last_month.;
length Flag $15. Flag1 $15.;
SET New_in;
Flag='Monthly_Ins';
Flag1='Total_Ins';
Date="&last_month."d;
format Date date9.;
run;

data IMP.New_in_&last_month.;
length Flag $15. Flag1 $15.;
SET New_out;
Flag='Monthly_Outs';
Flag1='Total_Outs';
Date="&last_month."d;
format Date date9.;
run;

DATA IMP.TOTAL_INS_OUTS;
SET IMP.New_in_&last_month. IMP.New_in_&last_month.;
RUN;

DATA IMP.TOTAL_INS_OUTS_HIST;
SET XIMP.TOTAL_INS_OUTS_HIST IMP.TOTAL_INS_OUTS;
RUN;

Can we write the above query in loop or automatically runs for 6 months New Ins and Outs.

Any ideas is appreciated.thanks

[ChrisNZ]

Does this help?

proc sql noprint;

  select into max(END_OF_MONTH) into :last_month
  from FINAL_ANALYSISSEPT19 ; /* This is an awful table name. At least try FINAL_ANALYSIS_201909 */

  %let last_monthf=&sysfunc(intnx(month,&last_month, 0,e),date9.); 
  %let prev_monthf=&sysfunc(intnx(month,&last_month,-1,e),date9.);
  %put &last_month= &last_monthf= &prev_monthf=;

  create table NEW as
  select curr.*
  from FINAL_ANALYSIS_201909 (where=(END_OF_MONTH= "&last_monthf"d))  curr
         left join
       FINAL_ANALYSIS_201909 (where=(END_OF_MONTH= "&prev_monthf"d))  prev
         on curr.ACCNO = prev.ACCNO
   where prev.ACCNO is missing;

[BaalaRaaji]

This helps but not completely...I want to run the code for 6 months ...like below code but can't figure out how to get the previous month date .

%macro date_loop(start,end);
%let start=%sysfunc(inputn(&start,anydtdte9.));
%let end=%sysfunc(inputn(&end,anydtdte9.));
%let dif=%sysfunc(intck(month,&start,&end));
%do i=0 %to &dif;
%let date=%sysfunc(intnx(month,&start,&i,E),date9.);
%put &date;
proc sql;
create table NEW_&date. as
select curr.accno,curr.balance,curr.END_OF_MONTH
from Monthly_report (where=(END_OF_MONTH= "&date"d)) curr
left join
Monthly_report (where=(END_OF_MONTH= "&date"d)) prev /* here needs to update */
on curr.ACCNO = prev.ACCNO
where prev.ACCNO is missing;
quit;

%end;
%mend date_loop;

%date_loop(31JAN2019,31OCT2019)

[unison · Accepted solution]

Here's an example using sample data that might lead in the right direction:

*Getting data to simplify example...;
proc means nway missing noprint data=sashelp.prdsal2;
class state prodtype monyr;
var actual;
output out=sales(drop=_type_ _freq_ prodtype) sum=;
where prodtype='OFFICE';
run;

*Set end of current month.;
%let curr_month = '31DEC1995'd;
data _null_;
curr_month = &curr_month;
beg_month = "'"||put(intnx("month",&curr_month.,-6,"e"),date9.)||"'d";
call symput("beg_month",beg_month);
run;

*Limit sales to last 6 months.;
data have(where=(end_of_month between &beg_month. and &curr_month.));
set sales;
format end_of_month date9.;
end_of_month = intnx("month",monyr,0,"e");
if mod(_n_,5)=2 then delete; *simulate missing data -- this example set has an observation for each state and month...;
run;

*Macro;
%macro loop(dsin=/*input dataset*/, dsoutpfx=/*output data prefix*/);
%do i=1 %to 6;
data &dsoutpfx._IN_&i. &dsoutpfx._OUT_&i.;
merge 
	&dsin.(where=(end_of_month = intnx("month",&curr_month.,-&i.,"e")) in=OLD)
	&dsin.(where=(end_of_month = intnx("month",&curr_month.,-&i.+1,"e")) in=NEW);

by state;
if not(OLD and NEW);
if OLD and not NEW then output &dsoutpfx._OUT_&i.;
else if NEW and not OLD then output &dsoutpfx._IN_&i.;
run;
%end;
%mend;

%loop(dsin=have,dsoutpfx=outsets)

-unison

[BaalaRaaji]

Thank you unison.

That worked perfectly...