BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
foxrol94
Fluorite | Level 6

Hello,

I'd like to build a SAS macro programme to generates these SQL instructions on a single line. 
Thanks for your help.

DELETE from LIB_ONE.TEMP_TABLE WHERE DAR='$DAR' AND VLD_ENR =1 AND SOURCE ='OP_Z' AND LET_OPE IN ('LEN','SELL') AND TIC_ENT IN ('107','102') AND ISIN IN ('BE00ZZZ', 'BE00YYYYYYY', 'BE001192XXX');
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

@foxrol94 wrote:

Hello,

I'd like to build a SAS macro programme to generates these SQL instructions on a single line. 
Thanks for your help.

DELETE from LIB_ONE.TEMP_TABLE WHERE DAR='$DAR' AND VLD_ENR =1 AND SOURCE ='OP_Z' AND LET_OPE IN ('LEN','SELL') AND TIC_ENT IN ('107','102') AND ISIN IN ('BE00ZZZ', 'BE00YYYYYYY', 'BE001192XXX');

I suspect there is more to your actual problem. 

 

But to generate that STATEMENT (lines don't have any importance to either the macro processor or the SQL language) you could just define the macro like this:

%macro mymacro;
DELETE from LIB_ONE.TEMP_TABLE WHERE DAR='$DAR' AND VLD_ENR =1 AND SOURCE ='OP_Z' AND LET_OPE IN ('LEN','SELL') AND TIC_ENT IN ('107','102') AND ISIN IN ('BE00ZZZ', 'BE00YYYYYYY', 'BE001192XXX');
%mend;

Then you could call the macro where ever you want the statement inserted.

proc sql;
 %mymacro
quit;

You might want to leave the semicolon out of the body of the macro and instead add it in the program that executes the macro.

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26

@foxrol94 wrote:

 

I'd like to build a SAS macro programme to generates these SQL instructions on a single line. 

Macros create dynamic code, that can change as needed without a human typing new code. What part of this code needs a macro?

 

Why does the code have to be on a single line?

--
Paige Miller
Tom
Super User Tom
Super User

@foxrol94 wrote:

Hello,

I'd like to build a SAS macro programme to generates these SQL instructions on a single line. 
Thanks for your help.

DELETE from LIB_ONE.TEMP_TABLE WHERE DAR='$DAR' AND VLD_ENR =1 AND SOURCE ='OP_Z' AND LET_OPE IN ('LEN','SELL') AND TIC_ENT IN ('107','102') AND ISIN IN ('BE00ZZZ', 'BE00YYYYYYY', 'BE001192XXX');

I suspect there is more to your actual problem. 

 

But to generate that STATEMENT (lines don't have any importance to either the macro processor or the SQL language) you could just define the macro like this:

%macro mymacro;
DELETE from LIB_ONE.TEMP_TABLE WHERE DAR='$DAR' AND VLD_ENR =1 AND SOURCE ='OP_Z' AND LET_OPE IN ('LEN','SELL') AND TIC_ENT IN ('107','102') AND ISIN IN ('BE00ZZZ', 'BE00YYYYYYY', 'BE001192XXX');
%mend;

Then you could call the macro where ever you want the statement inserted.

proc sql;
 %mymacro
quit;

You might want to leave the semicolon out of the body of the macro and instead add it in the program that executes the macro.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 2 replies
  • 277 views
  • 0 likes
  • 3 in conversation