topic Re: To create a macro call routine in SAS Programming

To create a macro call routine

ambadi007 — Thu, 02 Jun 2016 16:58:26 GMT

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

Where claims NOT IN PI call routine

Please help me on this.

Waiting for the valuable replies.

Manesh

Re: To create a macro call routine

Haikuo — Thu, 02 Jun 2016 17:08:11 GMT

CALL EXECUTE

DOSUBL if you have SAS 9.4

Re: To create a macro call routine

Astounding — Thu, 02 Jun 2016 17:16:58 GMT

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.

Re: To create a macro call routine

ambadi007 — Thu, 02 Jun 2016 17:44:48 GMT

Hi I need a macro to identify this

Re: To create a macro call routine

Reeza — Thu, 02 Jun 2016 17:54:59 GMT

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

Re: To create a macro call routine

ambadi007 — Fri, 03 Jun 2016 05:26:36 GMT

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

Re: To create a macro call routine

andreas_lds — Fri, 03 Jun 2016 07:48:09 GMT

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.

Re: To create a macro call routine

Reeza — Fri, 03 Jun 2016 08:36:45 GMT

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.

Re: To create a macro call routine

Cynthia_sas — Fri, 03 Jun 2016 14:20:34 GMT

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

Re: To create a macro call routine

ambadi007 — Mon, 06 Jun 2016 12:45:58 GMT

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

Re: To create a macro call routine

Reeza — Mon, 06 Jun 2016 16:49:18 GMT

Post sample data and expected output.

Re: To create a macro call routine

ambadi007 — Mon, 06 Jun 2016 17:15:46 GMT

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

Re: To create a macro call routine

Reeza — Mon, 06 Jun 2016 20:19:58 GMT

I don't think thats how a macro works. You can probably make it that way but there's better methods. How are you planning to call the macro?

Re: To create a macro call routine

ambadi007 — Tue, 07 Jun 2016 02:55:14 GMT

I want to call the macro like this

&lob as LOB

cd_claim as claim

and then the LOB should show wether the claim is CI or PI

Re: To create a macro call routine

Reeza — Tue, 07 Jun 2016 03:23:39 GMT

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;

Re: To create a macro call routine

ambadi007 — Tue, 07 Jun 2016 03:39:56 GMT

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

Re: To create a macro call routine

Reeza — Tue, 07 Jun 2016 07:49:00 GMT

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;

Re: To create a macro call routine

ambadi007 — Wed, 08 Jun 2016 05:19:17 GMT

Hi Reeza,

Thanks a lot for the soulution i used this code it created successfully . Thanks

Manesh