topic Re: dynamic queries in New SAS User

dynamic queries

sanjaymane7 — Mon, 25 May 2020 09:41:22 GMT

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

Re: dynamic queries

Kurt_Bremser — Mon, 25 May 2020 17:12:33 GMT

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.

Re: dynamic queries

Reeza — Mon, 25 May 2020 16:03:56 GMT

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.

@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