Beginner Question - Running Same Proc SQL code multiple times, but with different where statements

[mike_dee08]

Hello,

I am new to learning SAS, but have previous background in SQL.  I pull claims data almost daily and I am looking for a way to be more effective, as opposed to running the same code over and over.

For example, I am pulling claims data with procedure code 'X', but in the same SAS code, I will pull claims data with procedure code 'Y'.  Essentially, duplicating the Proc SQL, with the only change that would need to be made is in the Where statement - Procedure_Code = 'X'. 

I have been researching Do Loops, but I am not sure if this is the route I need to go.  Any suggestions?

Thank you,

Mike

[Reeza]

---

@mike_dee08 wrote:

Hello,

I am new to learning SAS, but have previous background in SQL.  I pull claims data almost daily and I am looking for a way to be more effective, as opposed to running the same code over and over.

For example, I am pulling claims data with procedure code 'X', but in the same SAS code, I will pull claims data with procedure code 'Y'.  Essentially, duplicating the Proc SQL, with the only change that would need to be made is in the Where statement - Procedure_Code = 'X'. 

I have been researching Do Loops, but I am not sure if this is the route I need to go.  Any suggestions?

Thank you,

Mike

---

What's the ultimate goal? Why can't you include the multiple diagnosis in a single PROC SQL query?

[mike_dee08]

Hi Reeza,

The ultimate goal is to include other variables as well.  I know procedure code might have been a little too simple, but I am looking to do the same thing with groups, subgroups, states, counties, etc.  When you add up the variables, it can get pretty tedious to continue to perform updates.

A lot of the information I have found on Do Loops runs calculations within the data set, or creates variables, but I have not seen a way to incorporate something like this into a Proc SQL Where function.

Thank you,

Mike

[Reeza]

I am so lost...perhaps it's worth posting what you have and what you need instead. And explaining your full problem. 

[mike_dee08]

Hi Reeza,

Hopefully this helps to explain things. 

In this example, I currently have macros set up like the ones below.  However, I have to manually update these macros and re-run the SAS code for each of the 12 states that I need information for.  I would like to automate my SAS code to perform some sort of loop where the first time through the code it will use the AZ information listed below, then the second time through the code it will use the CA information listed below, and so on.

%let state='AZ';

%let parameter_values = ('AZGroup2','AZGroup4','AZGroup6)

%let filename =Path\AZGroups

%let state='CA';

%let parameter_values = ('CAGroup1','CAGroup4','CAGroup7)

%let filename =Path\CAGroups

Let me know if you need any other information that might help.

Thank you,

Mike

[Reeza]

Put that information into a data set.

Use CALL EXECUTE to call the macro for every line of data in your data set using a data _null_ step. 

The documentation has a fully worked example.

---

@mike_dee08 wrote:

Hi Reeza,

Hopefully this helps to explain things. 

In this example, I currently have macros set up like the ones below.  However, I have to manually update these macros and re-run the SAS code for each of the 12 states that I need information for.  I would like to automate my SAS code to perform some sort of loop where the first time through the code it will use the AZ information listed below, then the second time through the code it will use the CA information listed below, and so on.

%let state='AZ';

%let parameter_values = ('AZGroup2','AZGroup4','AZGroup6)

%let filename =Path\AZGroups

%let state='CA';

%let parameter_values = ('CAGroup1','CAGroup4','CAGroup7)

%let filename =Path\CAGroups

Let me know if you need any other information that might help.

Thank you,

Mike

---