Hi,
I have a table like the following (named test) :
Account | Type | Amount |
---|---|---|
11111 | CO | 999 |
22222 | PH | 9999 |
33333 | TIP | 12034 |
33333 | CO | 1345156 |
33333 | PH | 12314 |
44444 | CO | 23414 |
And another table named cl_prior_accounts from which I want to pull all account and lookup if I have data in my table test
I run the following codeL
PROC SQL;
CREATE TABLE cl_prior.cl_prior_ent_final AS SELECT
cl_prior_accounts.Accounts,
(case when test.type = "CO" then test.amount end) as SUM_OF_CO_ACC,
(case when test.type = "PH" then test.amount end) as SUM_OF_PH_ACC,
(case when test.type = "TIP" then test.amount end) as SUM_OF_TIP_ACC
FROM mylib.cl_prior_accounts AS cl_prior_accounts
LEFT JOIN mylib.test as test ON (cl_prior_accounts.accounts = test.accounts)
;QUIT;
For account number 33333 I get three row of data; one for each type of account. I would like to have all that information on one line for account 33333. How do I do that?
I still want to use left join in order to have all my accounts from cl_prior_accounts table.
Thank you for your help!
Try adding a group by on the accounts
Thank you reeza,
Group by did the work. You just have to use sum( ) on the case statement in order for them to be grouped.
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!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.