BookmarkSubscribeRSS Feed
SaschaD
Obsidian | Level 7

Hello,

 

my code below works but how can I improve it?

Is the case syntax the best choice? Or should I use the IF statement?

 

PROC SQL;
	UPDATE GRV_Export_Dummy D
		SET	'PAY'n = (CASE 
						WHEN 	(SELECT 'EB'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1'
						THEN 'EB/'
						ELSE ''
					END) 
					||
					(CASE 
						WHEN 	(SELECT 'M01'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1'
						THEN 'M/'
						ELSE ''
					END)
					||
					(CASE 
						WHEN	 (SELECT 'V01'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'V02'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'V03'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1'
						THEN 'V/'
						ELSE ''
					END)
					||
					(CASE 
						WHEN 	(SELECT 'H01'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'H02'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'H03'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'H04'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'H05'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'H06'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1'
						THEN 'H/'
						ELSE ''
					END)
					||
					(CASE 
						WHEN	 (SELECT 'J01'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'J02'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'J03'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'J04'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'J05'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'J06'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'J07'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'J08'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'J09'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'J10'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'J11'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1' &&
							 (SELECT 'J12'n FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR) = '1'
						THEN 'J/'
						ELSE ''
					END)
	WHERE D.GRV IN (SELECT V.VTGONR FROM SERVER.VIEW_TABLE V WHERE D.GRV = V.VTGONR);
QUIT;

 

Additional I don't know how can I use a "nested case"? 

For example 

IF V01 = 1 THEN V01

IF V02 = 1 THEN V02

IF V03 = 1 THEN V03

IF V01 & VO2 & VO3 = 1 THEN V

 

Thanks,

Sascha

1 REPLY 1
Reeza
Super User
Your code isn't really legible in the format you've posted it - a single line. The editor mangled it somehow, most likely.

From the IF statements it looks like an ARRAY and data step would be more efficient but hard to say without fully seeing the code.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1 reply
  • 315 views
  • 0 likes
  • 2 in conversation