dynamic queries

[sanjaymane7]

Hi,

I have excel file of intermediary code who does insurance business and want to give commission as per their business amount, location, product, channel, make and model.

I have already made query for individual intermediary code basis of if condition and it’s working fine.

Now, list is increased by 2000 records and very difficult to right individual query to each intermediary code. I am trying through CATX function which is also not manageable after certain limit as combination is being increased day by day.

I want to learn dynamic query. Please share any demonstration video and inform about it.

Also, sharing an example SAS table for your reference.

IMD_Code	Location	Product	Make	Model	Business	Channel	Commission %
1234							10
1235	Mumbai						5
1236	Pune	Pvt					7
1237	Delhi	Pvt	Maruti	Alto			12
1238	Delhi	Pvt	Maruti	All	> 100000		19.5
1239	Mumbai	Pvt				Motor PC	19.5
1240	Mumbai	Tw				Motor TW	22.5

[Kurt_Bremser]

Please provide SAS datasets as data steps with datalines, so we can easily recreate your data in our SAS sessions without doubt about contents and variable attributes.

Please do also supply an example for what you want to get out of that input dataset.

Also show the code you already have.

[Reeza]

You want a macro, with specific parameters. You can then drive it from your data set using CALL EXECUTE. 

An example of how to do this is here: https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Start with your most basic program assuming you'll have the parameters needed and then add in conditional logic to deal with all the different parameter combinations you need. 

Spoiler

---

@sanjaymane7 wrote:

Hi,

I have excel file of intermediary code who does insurance business and want to give commission as per their business amount, location, product, channel, make and model.

I have already made query for individual intermediary code basis of if condition and it’s working fine.

Now, list is increased by 2000 records and very difficult to right individual query to each intermediary code. I am trying through CATX function which is also not manageable after certain limit as combination is being increased day by day.

I want to learn dynamic query. Please share any demonstration video and inform about it.

Also, sharing an example SAS table for your reference.

IMD_Code	Location	Product	Make	Model	Business	Channel	Commission %
1234							10
1235	Mumbai						5
1236	Pune	Pvt					7
1237	Delhi	Pvt	Maruti	Alto			12
1238	Delhi	Pvt	Maruti	All	> 100000		19.5
1239	Mumbai	Pvt				Motor PC	19.5
1240	Mumbai	Tw				Motor TW	22.5

---