Pulling data for multiple months and then merging them

[TheNovice]

For a month i need to pull a summary view like this. I need to pull this month over month and join the counts for each month (t01_2017) to this original table...

BL_CONS_IND	INDICATOR	COL_NQ_STATUS	t01_2017
	F	N	2431
N	F	D	218472
N	F	N	1600066
N	B	D	297352
N	B	N	1741985
Y	F	D	10
Y	F	N	40
Y	R	D	186061
Y	R	N	1331190

This is what i have put together but  I am not sure if this is the right approach and i don't know how to proceed further...

libname d2017 "/sasdatacredit/data/drssblr0717/Historical_Data/AB/&Year.";

%MACRO EXTRACT_ELIG(month=,Year=,day=);

libname d2017 "/sasdatacredit/data/drssblr0717/Historical_Data/AB/&Year.";

PROC SQL;
CREATE TABLE DEL_&month._&year. AS
SELECT BL_CONS_IND,INDICATOR,COL_INQ_STATUS,COUNT(COL_INQ_STATUS) as t&month._&year.
FROM D2017.AB_&Year.&month.&day.
WHERE A_TYPE||A_SUB_TYPE = 'IR' AND STATUS IN ('O','S') AND VE = 'Y'
GROUP BY BL_CONS_IND,INDICATOR,COL_INQ_STATUS
;
QUIT;

%MEND;
%EXTRACT_ELIG(month=01,Year=2017,day=31);
%EXTRACT_ELIG(month=02,Year=2017,day=28);
%EXTRACT_ELIG(month=03,Year=2017,day=31);
%EXTRACT_ELIG(month=04,Year=2017,day=30);
%EXTRACT_ELIG(month=05,Year=2017,day=31);
%EXTRACT_ELIG(month=06,Year=2017,day=30);
%EXTRACT_ELIG(month=07,Year=2017,day=31);

With this i can run all the months fairly quick... however i want to create a single table with the counts joined by month... not sure how to achieve that... any ideas? Not experienced with macros...

[Reeza]

1. Add a step at the end that appends the results to a main table and deletes your intermediary table. Use PROC APPEND.

2. Add a date variable so that you can determine which dataset it was sourced from

3. If you really want a column for each summary stat, use PROC TRANSPOSE after to transpose the data sets. Otherwise keeping the summary counts with the same variable name is more efficient. 

Try this:

%MACRO EXTRACT_ELIG(month=,Year=,day=);
 
libname d2017 "/sasdatacredit/data/drssblr0717/Historical_Data/AB/&Year.";

PROC SQL;
CREATE TABLE _temp AS
SELECT BL_CONS_IND,INDICATOR,COL_INQ_STATUS,COUNT(COL_INQ_STATUS) as MEASURE, mdy(&month, &day, &year) as date
FROM D2017.AB_&Year.&month.&day.
WHERE A_TYPE||A_SUB_TYPE = 'IR' AND STATUS IN ('O','S') AND VE = 'Y'
GROUP BY BL_CONS_IND,INDICATOR,COL_INQ_STATUS
;
QUIT;

*add data to main table;
proc append base=results data=_temp force;
run;

*delete temporary table;
proc sql;
drop table _temp;
run;



%MEND;

---

@TheNovice wrote:

For a month i need to pull a summary view like this. I need to pull this month over month and join the counts for each month (t01_2017) to this original table...

BL_CONS_IND	INDICATOR	COL_NQ_STATUS	t01_2017
	F	N	2431
N	F	D	218472
N	F	N	1600066
N	B	D	297352
N	B	N	1741985
Y	F	D	10
Y	F	N	40
Y	R	D	186061
Y	R	N	1331190

This is what i have put together but  I am not sure if this is the right approach and i don't know how to proceed further...

libname d2017 "/sasdatacredit/data/drssblr0717/Historical_Data/AB/&Year.";

%MACRO EXTRACT_ELIG(month=,Year=,day=);

libname d2017 "/sasdatacredit/data/drssblr0717/Historical_Data/AB/&Year.";

PROC SQL;
CREATE TABLE DEL_&month._&year. AS
SELECT BL_CONS_IND,INDICATOR,COL_INQ_STATUS,COUNT(COL_INQ_STATUS) as t&month._&year.
FROM D2017.AB_&Year.&month.&day.
WHERE A_TYPE||A_SUB_TYPE = 'IR' AND STATUS IN ('O','S') AND VE = 'Y'
GROUP BY BL_CONS_IND,INDICATOR,COL_INQ_STATUS
;
QUIT;

%MEND;
%EXTRACT_ELIG(month=01,Year=2017,day=31);
%EXTRACT_ELIG(month=02,Year=2017,day=28);
%EXTRACT_ELIG(month=03,Year=2017,day=31);
%EXTRACT_ELIG(month=04,Year=2017,day=30);
%EXTRACT_ELIG(month=05,Year=2017,day=31);
%EXTRACT_ELIG(month=06,Year=2017,day=30);
%EXTRACT_ELIG(month=07,Year=2017,day=31);

With this i can run all the months fairly quick... however i want to create a single table with the counts joined by month... not sure how to achieve that... any ideas? Not experienced with macros...

---

[Astounding · Accepted solution]

One question that's not entirely clear ... are you extracting all this data purely for purposes of making this report, or will you need it for other purposes as well?

At any rate, it seems like you're not really sure how to get summary counts from your data.  Here's what I would recommend.  Add to your macro just after the QUIT statement:

proc freq data=DEL_&month._&year.;
tables BL_CONS_IND * INDICATOR * COL_NQ_STATUS / noprint
   out=summary_counts_&year._&mo (rename=(count=t&month._&year.) drop=percent);
run;

Then after running the macro as many times as needed, combine the summaries:

data want;
merge summary_counts_: ;
by BL_CONS_IND INDICATOR COL_NQ_STATUS;
run;

There are definitely other ways that do not require running the macro many times.  But this approach is closest to using what you have already written.

[TheNovice]

Thank you! Apologies for not replying earlier