Help using Base SAS procedures

SQL query in SAS help

Reply
Regular Contributor
Posts: 186

SQL query in SAS help

Hi,

I have a table like the following (named test) :

AccountTypeAmount
11111CO999
22222PH9999
33333TIP12034
33333CO1345156
33333PH12314
44444CO23414

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!

Super User
Posts: 17,868

Re: SQL query in SAS help

Try adding a group by on the accounts

Regular Contributor
Posts: 186

Re: SQL query in SAS help

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.

Ask a Question
Discussion stats
  • 2 replies
  • 170 views
  • 0 likes
  • 2 in conversation