topic Re: case when combining left join in SAS Programming

case when combining left join

HeatherNewton — Sun, 13 Mar 2022 11:09:36 GMT

SELECT A.*,
CASE 
WHEN A.ORG CODE IN ('251' '301' '302' '303') THEN 'HKG'
WHEN A.ORG_ CODE IN ('306' '307' '308' '309') THEN 'CHN'
ELSE 'MAC'
END AS PORTFOLIO,
B.RELATIONSHIP NO,
B.IND_CENTRAL AS IND_CENTRAL,
B.PBI AS PBI_PIMIT,
B.CREDIT_LIMIT AS SAMP_CRLIM,
B.CURRENT_BAL AS SAMP_OS
FROM ACCT_NEW_ALL A
LEFT JOIN ACCT_LIST_SAMP B
ON AR.ORG_CODE=B.ORG_CODE
AND R.BCCOUNT_NO=B.ACCOUNT_NO;
QUIT;

I understand case when and left join but putting together I cannot understand especially the line end as portfolio, I thought normally case when finish at the word end, now it continues with as portfolio and more, could you help me to interpret?

Re: case when combining left join

PaigeMiller — Sun, 13 Mar 2022 11:21:01 GMT

CASE 
WHEN A.ORG CODE IN ('251' '301' '302' '303') THEN 'HKG'
WHEN A.ORG_ CODE IN ('306' '307' '308' '309') THEN 'CHN'
ELSE 'MAC'
END AS PORTFOLIO

this indicates that the result of the CASE WHEN will be a variable named PORTFOLIO

Re: case when combining left join

HeatherNewton — Sun, 13 Mar 2022 11:44:14 GMT

then what is going on after the end as portfolio statement

suddenly a few variable names and then some left join, how to read this left join statement?

Re: case when combining left join

Kurt_Bremser — Sun, 13 Mar 2022 12:31:26 GMT

This code will not run anyway, as it throws errors here:

ON AR.ORG_CODE=B.ORG_CODE
AND R.BCCOUNT_NO=B.ACCOUNT_NO

There are no aliases AR or R in the FROM clause defined.

Shouting at the SAS interpreter won't improve things.

Re: case when combining left join

PaigeMiller — Sun, 13 Mar 2022 13:33:40 GMT

@HeatherNewton wrote:

then what is going on after the end as portfolio statement

suddenly a few variable names and then some left join, how to read this left join statement?

You could be specific and show us the LOG (the entire log for this PROC SQL) and point out the problems you see, instead of a vague question like "what is going on".

Please, from now on, when you ask questions, be specific; and when there is a problem, show us the ENTIRE log for this PROC or DATA step, don't make us ask.

Re: case when combining left join

Tom — Sun, 13 Mar 2022 16:43:07 GMT

Formatting the code might help you read and understand it. When you have to split a statement into multiple lines place the continuation character or keyword at the START of the new line where it will be easier for a human scanning the code to see it. That includes the final semi-colon that marks the end of the statement.

SELECT A.*
     , CASE WHEN A.ORG CODE IN ('251' '301' '302' '303') THEN 'HKG'
            WHEN A.ORG_ CODE IN ('306' '307' '308' '309') THEN 'CHN'
            ELSE 'MAC'
       END AS PORTFOLIO
     , B.RELATIONSHIP NO
     , B.IND_CENTRAL AS IND_CENTRAL
     , B.PBI AS PBI_PIMIT
     , B.CREDIT_LIMIT AS SAMP_CRLIM
     , B.CURRENT_BAL AS SAMP_OS
FROM ACCT_NEW_ALL A
LEFT JOIN ACCT_LIST_SAMP B
  ON AR.ORG_CODE=B.ORG_CODE
  AND R.BCCOUNT_NO=B.ACCOUNT_NO
;

So now it is more obvious that the list separated by the commas is the list of variables to select. A.* is all of the variables from dataset named A (or the dataset that is using the ALIAS of A). Then the CASE..END statement value is being given the name of PORTFOLIO. Then there is a list of other variables to select.

The one that does not follow NAME AS NAME pattern is that B.RELATIONSHIP variable. In that one the keyword AS is not present. Did they want to use NO as the variable name? Then it needs the AS keyword. If they meant NO as a LABEL then it needs the LABEL or LABEL= keyword. Or did they just type a space instead of an underscore and the variable name should be RELATIONSHIP_NO? Or are they using non-standard names and they should have used a name literal, like b.'RELATIONSHIP NO'n, in the code?

The ON condition is referencing variables from two datasets that are not part of the query. AR and R. Should those be changed to A? Or are there more datasets that need to contribute to this query.