BookmarkSubscribeRSS Feed
HeatherNewton
Quartz | Level 8
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?

5 REPLIES 5
PaigeMiller
Diamond | Level 26
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

--
Paige Miller
HeatherNewton
Quartz | Level 8

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?

PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller
Kurt_Bremser
Super User

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.

Tom
Super User Tom
Super User

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.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

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
  • 5 replies
  • 1077 views
  • 1 like
  • 4 in conversation