Desktop productivity for business analysts and programmers

Using data step how can I use the IF then like SQl join in SAS Enterprise guide

Accepted Solution Solved
Reply
Contributor
Posts: 74
Accepted Solution

Using data step how can I use the IF then like SQl join in SAS Enterprise guide

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


Accepted Solutions
Solution
‎04-01-2018 12:18 AM
Super User
Super User
Posts: 8,279

Re: Using data step how can I use the IF then like SQl join in SAS Enterprise guide

Posted in reply to Sourav_sas

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


All Replies
Contributor
Posts: 74

Re: Using data step how can I use the IF then like SQl join in SAS Enterprise guide

Posted in reply to Sourav_sas
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
Solution
‎04-01-2018 12:18 AM
Super User
Super User
Posts: 8,279

Re: Using data step how can I use the IF then like SQl join in SAS Enterprise guide

Posted in reply to Sourav_sas

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;

 

Contributor
Posts: 74

Re: Using data step how can I use the IF then like SQl join in SAS Enterprise guide

thanks Tom

It helped me to solve my coding issue..

 

Thanks

Regards

Sourav

☑ This topic is solved.

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

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