BookmarkSubscribeRSS Feed
nicnad
Fluorite | Level 6

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!

2 REPLIES 2
Reeza
Super User

Try adding a group by on the accounts

nicnad
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

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
  • 2 replies
  • 683 views
  • 0 likes
  • 2 in conversation