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

Hi

 

Can I get a quick solution on this

 

I am using Inner join in the following code,

PROC SQL;
		CREATE Table WORK.TECH1 AS 
			SELECT 	a.*						,
				B.IP_SICSID  				,
				b.MAIN_CURRISOALPHA3_ID		,
			 	CASE WHEN b.LEVEL_OF_BUS_ID = "IAB" THEN "inward"	
					 WHEN b.LEVEL_OF_BUS_ID = "ORP" THEN "outward"
	                 ELSE '' 
				END as inw_outw format $10. ,
			 	b.BUS_ID					,
			    b.TYPE_OF_BUS_ID			,
			 	b.BUS_TTL					,
				%IF &PROFIT_CENTER = Y %THEN %DO;
					b.PRFT_CENT_TTL         ,
				%END;
		/* DWH-2811 Change starts */
				%IF &INSURED_ID_TTL = Y %THEN %DO;
					b.INS_ID                ,
					b.INS_TTL               ,
				%END;
				%IF &IP_FROM_TO = Y %THEN %DO;
					b.IP_FROM               ,
					b.IP_TO                 ,
				%END;
				b.PAYM_PARTN_ID             ,
				b.PAYM_PARTN_TTL            ,
				b.ACCOUNT_GROUP_ID          ,
				b.LEVEL_OF_BUS_ID 			,
				b.BUS_PARTN_ID_BOOK			,
				b.ORIG_CDNT_ID				,
				b.ORIG_CDNT_TTL 			,
				b.ORIG_CDNT_COUNTRY			 
			FROM WORK.AGING_TECHNICAL1 a INNER JOIN 
				WORK.IP_SECT_SMALL b on
				a.SECTION_SICSID=b.SECT_SICSID 
	;
	QUIT;

Here all '&' variables are parameter macro variable, if the value of that variable is the defined value inside quote, then we will get that column,

 

I need to use this join inside a data step, merge statement,

I am using the code like

Proc sort data= AGING_TECHNICAL1;
By SECTION_SICSID;
Run;

Proc Sort Data=  IP_SECT_SMALL;
By SECT_SICSID;
Run;

	Data IP_SECT_SMALL
	 (Rename= (SECT_SICSID=SECTION_SICSID )
					    );
	Set IP_SECT_SMALL;
	Drop RETROCESS_ID CDNT_ID;
	Run;

Data Tech;
		Attrib inw_outw Length= $10.;
		Set Tech1;
		Select;
		When (LEVEL_OF_BUS_ID = "IAB") inw_outw= "inward";
		When (LEVEL_OF_BUS_ID = "ORP") inw_outw= "outward";
		End;
	Run;

But I am not able to define the if then logic from the SQL join in the data step,  for example this part

 

%IF &PROFIT_CENTER = Y %THEN %DO;
					b.PRFT_CENT_TTL         ,
				%END;

How can I define this one, because I need to call this variable based on that sort parameter (&PROFIT_CENTER) is Y,

Can you please help me to construct this code on Data step or merge statement.

I am in little bit hurry. 

 

 

Thanks

Regards

Sourav

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

If you want to determine which variables are included in the output dataset the use a KEEP (or DROP) statement.

So you might do it this way.

%IF NOT (&PROFIT_CENTER = Y) %THEN %DO;
  DROP PRFT_CENT_TTL  ;
%END;

 

View solution in original post

3 REPLIES 3
Sourav_sas
Quartz | Level 8
If anyone can help me here, then come up with some solution. As I really need that here to complete my coding. It is bit urgent here.

Thanks
Regards
Sourav
Tom
Super User Tom
Super User

If you want to determine which variables are included in the output dataset the use a KEEP (or DROP) statement.

So you might do it this way.

%IF NOT (&PROFIT_CENTER = Y) %THEN %DO;
  DROP PRFT_CENT_TTL  ;
%END;

 

Sourav_sas
Quartz | Level 8

thanks Tom

It helped me to solve my coding issue..

 

Thanks

Regards

Sourav

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1518 views
  • 0 likes
  • 2 in conversation