DATA Step, Macro, Functions and more

To create a macro call routine

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

To create a macro call routine

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

 

 


Accepted Solutions
Solution
‎06-06-2016 02:02 AM
SAS Super FREQ
Posts: 8,740

Re: To create a macro call routine

[ Edited ]

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: To create a macro call routine

CALL EXECUTE

 

or

 

DOSUBL if you have SAS 9.4

Super User
Posts: 5,071

Re: To create a macro call routine

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.

Contributor
Posts: 60

Re: To create a macro call routine

Hi I need a macro to identify this

Super User
Posts: 17,750

Re: To create a macro call routine

[ Edited ]

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 

 

Contributor
Posts: 60

Re: To create a macro call routine

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

 

 

 

 

 

Super Contributor
Posts: 259

Re: To create a macro call routine

[ Edited ]

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.

Super User
Posts: 17,750

Re: To create a macro call routine

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. 

Solution
‎06-06-2016 02:02 AM
SAS Super FREQ
Posts: 8,740

Re: To create a macro call routine

[ Edited ]

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

Contributor
Posts: 60

Re: To create a macro call routine

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

 

Super User
Posts: 17,750

Re: To create a macro call routine

Post sample data and expected output.

Contributor
Posts: 60

Re: To create a macro call routine

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

 

 

Super User
Posts: 17,750

Re: To create a macro call routine

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;
Contributor
Posts: 60

Re: To create a macro call routine

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

Super User
Posts: 17,750

Re: To create a macro call routine

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;

 

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 620 views
  • 3 likes
  • 6 in conversation