To create a macro call routine

[ambadi007]

Hi Team,

Ii want to create a macro call routine the synario is :-

I have a table which is Personal Insurance Claims. I need to create a macro using all the this (PI) claims  and need to use this macro in my program to check weather this is PI or other type of claims .

The code condition should be

Where Claims IN PI call routine

                 or

 Where claims NOT IN PI call routine

Please help me on this.

Waiting for the valuable replies.

Manesh

[Haikuo]

CALL EXECUTE

or

DOSUBL if you have SAS 9.4

[Astounding]

You'll need to do a little more work before anyone can write a macro.  Give an example of what the program would look like without macros, and lots of people here can help you turn it into a macro.  It's entirely possible that this would be a simple SQL application that doesn't need macros at all.

[ambadi007]

Hi I need a macro to identify this

[Reeza]

What's "this"?

Please take the time to detail your issue if you want help. Without any details the answers is do "that".

EDIT:

Here are some resources on how to ask a good question. Note the Minimum verifiable example.  At minimum include sample data and what you want as output.

http://stackoverflow.com/help/how-to-ask

http://stackoverflow.com/help/mcve 

[ambadi007]

Hi Team,

My client needs an additional column in the report to identify wether the insurace claim is Personal (PI) or Commercial (CI) . To identify the PI claims we have one PI claims table and If i want to fetch the PI claims only I can do with SQL joins . But here I need to create a macro with the PI claims from the PI_Table and use in to the code which i have . It should be taking all the PI claims from PI table and create a maco call routine . Thanks

Manesh

[andreas_lds]

I recommend reading https://support.sas.com/documentation/cdl/en/mcrolref/67912/HTML/default/viewer.htm#titlepage.htm, especially "Understanding and Using the Macro Facility".

A macro does not create columns, a macro generats sas code.

[Reeza]

A macro is used to dynamically generate code. 

Which portiom ion of your query is dynamic?

perhaps post your query, or a reasonable similar query, and highlight which part needs to be dynamic as well as how you,expect to call the macro. 

if its just different Where conditions look up the %if/%then macro logic in the documentation @andreas_lds referenced. 

[Cynthia_sas · Accepted solution]

Hi,
And, in addition to all the other excellent suggestions, if you are completely new to SAS macro processing, I recommend these 2 papers as a place to provide some context for what you're reading:
http://www2.sas.com/proceedings/sugi28/056-28.pdf
and
https://support.sas.com/resources/papers/proceedings13/120-2013.pdf

cynthia

[ambadi007]

HI Riza,

Sorry to bother you again on this . I think my explanation was not enough to help me for this . Actually i am working for insurance claims reporting which deals with commercial insurannce(CI) and personal Insurance (PI) . we have one perticular table to identify the PI claims and there now I want to create a macro which should identify if one claim is PI or CI . I tried the below code .

%lob_div= case Proc sql;select CL_claim_PI from PI_table then PI else CI end;

The business logic is if the claim id for one poliy is available in the PI_table then it is PI elase CI . so I need to extract the PI claims from the PI table and check if this claim is available in the other tables and ten it is PI elase CI

Jus need to differentiate these two to create a report . I just want to show the LOB_DIV (Line of business wether it is CI or PI) in the report

Thanks

Manesh

[Reeza]

Post sample data and expected output.

[ambadi007]

The output should show

Proc sql;

create table base as &LOB as LOB,claim_id as claim ettc..

The LOB column should show wether this claim is PI or CI

So that The LOB macro variable should fetch claim from the PI table and look up  the claims from other tables .

In simply lob=  The claims from PI table is matching with the claims from other table then PI else CI

Regards,

Manesh

[Reeza]

I'm not seeing the need for a macro here a simple join will work, or at worst case a format.

proc sql;
create table want as
select a.*, 
case when a.id=b.id then 'PI'
else 'CI' end as LOB
from have as a
left join lookup as b
on a.id=b.id;
quit;

[ambadi007]

HI Reeza,

Thanks for your reply and here I need a mcro because I have to use this logic to most of my programmes . That is the reason I asked a macro for this

[Reeza]

Create a format or function. The format Code can be added to your autoexec so it's refreshed every time SAS is started or scheduled to run every day if necessary. 

http://www2.sas.com/proceedings/sugi30/001-30.pdf

You can add an in line query which would make your code incredibly inefficient. 

Sample data would help in creating example code.

%macro lookup;

inline query here

%mend;

Then use it as 

proc SQL;

create table want as 

select a.id, %lookup, a.var

from have as a;

quit;