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
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
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;
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
Thank you unison.
That worked perfectly...
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: