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...
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.
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...
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.
Thank you! Apologies for not replying earlier
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.