Improve case satement and nested statements

SaschaD — Mon, 23 Sep 2019 15:41:29 GMT

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

Re: Improve case satement and nested statements

Reeza — Mon, 23 Sep 2019 15:01:13 GMT

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.

topic Improve case satement and nested statements in SAS Programming

Improve case satement and nested statements

Re: Improve case satement and nested statements