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

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!
SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 3 replies
  • 1017 views
  • 0 likes
  • 2 in conversation