1. Import your Excel file and join the files
See example query below, you didn't illustrate where End date comes from though so I just assumed that's a different macro variable and you can fill that in.
PROC SQL;
CREATE TABLE FULL_FILE AS
SELECT
ACCOUNT,
ACCOUNT_NAME,
PERIOD_ID,
SUM(COUNT_MON_NUM) AS MEMBERS
FROM
DATABASE.SR_MBR_MON AS A
LEFT JOIN EXCELFILE as B
WHERE A.ACCOUNT = B.ACCOUNT_NUMBER
AND A.PERIOD_ID BETWEEN B.START_DATE AND &END_DATE
GROUP BY 1,2
;QUIT;
2. Write a macro and use CALL EXECUTE
*assumes you have already imported the excel file;
%macro export_data(account_name = , start_date= , end_date=);
PROC SQL;
CREATE TABLE &Account_Name_&End_Date AS
SELECT
ACCOUNT,
ACCOUNT_NAME,
PERIOD_ID,
SUM(COUNT_MON_NUM) AS MEMBERS
FROM
DATABASE.SR_MBR_MON
WHERE ACCOUNT = &ACCOUNT_NUMBER
AND PERIOD_ID BETWEEN &START_DATE AND &END_DATE
GROUP BY 1,2
;QUIT;
%mend;
data make_reports;
set excelFile;
str = catt('%make_report(account_number = ',
account_number,
' , start_date = ',
start_date,
' , end_date = ',
end_date,
');');
call execute(str);
run;
UCLA introductory tutorial on macro variables and macros https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/ Tutorial on converting a working program to a macro This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md Examples of common macro usage https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716
... View more