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.
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
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
Perfect. Thanks Tom.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.