SQL query in SAS help

[nicnad]

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!

[Reeza]

Try adding a group by on the accounts

[nicnad]

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.