topic Re: Advice on how to modify an SQL Join expression (case statement) in SAS Data Management

Advice on how to modify an SQL Join expression (case statement)

TurnTheBacon — Fri, 12 Jul 2013 13:29:59 GMT

Data Integration Studio

Imagine that you have an SQL Join with the two following input tables:

INPUT 1:

SOFTWARE_ID	VERSION_ID	STATUS_CODE
1234	100	AAA
1234	101	AAE
1234	102	ABB
1234	103	ABF

INPUT 2:

SOFTWARE_ID	NUMERIC_VALUE
1234	1250,25

The SQL Join uses INNER JOIN on input1.SOFTWARE_ID=input2.SOFTWARE_ID

In the output table, the expression used for NUMERIC_VALUE is:

case

when input1.VERSION_ID = max(input1.VERSION_ID) then input2.NUMERIC_VALUE

else 0

end

This results in the following output table:

SOFTWARE_ID	VERSION_ID	STATUS_CODE	NUMERIC_VALUE
1234	100	AAA	0
1234	101	AAE	0
1234	102	ABB	0
1234	103	ABF	1250,25

MY PROBLEM:

I want to make sure that for each SOFTWARE_ID (there's many more than in this simplified example), the NUMERIC_VALUE is placed on the max VERSION_ID that has STATUS_CODE either AAA or AAE.

Basically something like this (if this worked):

case

when input1.VERSION_ID = max(input1.VERSION_ID where input1.STATUS_CODE in ("AAA","AAE")) then input2.NUMERIC_VALUE

else 0

end

I want the output to be like this:

SOFTWARE_ID	VERSION_ID	STATUS_CODE	NUMERIC_VALUE
1234	100	AAA	0
1234	101	AAE	1250,25
1234	102	ABB	0
1234	103	ABF	0

I'd greatly appreciate advice on how to modify the SQL JOIN expression used for NUMERIC_VALUE.

Thanks so much for your time.

Re: Advice on how to modify an SQL Join expression (case statement)

Tom — Sat, 13 Jul 2013 00:13:54 GMT

How about using the if inside of the MAX() function.

You could take advantage of SAS's method of evaluating boolean expressions as 0 or 1 :

case when input1.VERSION_ID = max( input1.VERSION_ID * (input1.STATUS_CODE in ("AAA","AAE")) )

then input2.NUMERIC_VALUE

else 0

end

Or just use another CASE statement inside the MAX() function.

case when input1.VERSION_ID = max(

case when (input1.STATUS_CODE in ("AAA","AAE"))

then input1.VERSION_ID

else 0

end )

then input2.NUMERIC_VALUE

else 0

end

Re: Advice on how to modify an SQL Join expression (case statement)

TurnTheBacon — Sat, 13 Jul 2013 14:18:55 GMT

Perfect. Thanks Tom.