BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ambadi007
Quartz | Level 8

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

17 REPLIES 17
Haikuo
Onyx | Level 15

CALL EXECUTE

 

or

 

DOSUBL if you have SAS 9.4

Astounding
PROC Star

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
Quartz | Level 8

Hi I need a macro to identify this

Reeza
Super User

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
Quartz | Level 8

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
Jade | Level 19

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
Super User

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
SAS Super FREQ

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
Quartz | Level 8

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
Super User

Post sample data and expected output.

ambadi007
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User

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;

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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