SAS Question: Loop through list of different criteria for one query

Pattyp33 — Thu, 15 Jul 2021 20:12:48 GMT

Hi everyone,

I was researching a lot of forums and for some reason I can't seem to find what I'm looking for so I'm posting here. Apologies if I miss any details. I also won't be able to share too many details since I work with PHI data.

What I'm ultimately trying to accomplish is creating a snippet of code that will loop through a list of accounts and start dates and pull information from one designated query where the account and start dates will change based on what is imported from the excel file. I will note here, the excel file is on a shared location and is updated each month to add or remove clients that need to be tracked

As an example, here is what the excel (in it's simplest form) looks like:

A user can go in and either remove or add clients to this list. So the first "client" listed here, we would pull information starting from 202104 to current (&End_Date) which is already calculated but the second "Client" on here would need to have their information pulled starting in 201905

Account_Name	Account_Number	Start_Date
Alpha	1A1234B	202104
Beta	1B5489H	201905
Gamma	1C5421J	202101
Delta	1W6811C	202012
Epsilon	1G1234U	201911

The code pasted below is what I'm trying to update. Here the &Account_Number and &Start_Date would need change as the loop processes until it reaches the end of the list. Each client will have it's own output table

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;

Any help would be greatly appreciated and please let me know if I need to be more specific with my ask - thank you in advanced!

Re: SAS Question: Loop through list of different criteria for one query

Reeza — Thu, 15 Jul 2021 20:25:31 GMT

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

topic SAS Question: Loop through list of different criteria for one query in SAS Programming

SAS Question: Loop through list of different criteria for one query

Re: SAS Question: Loop through list of different criteria for one query