DATA Step, Macro, Functions and more

Proc SQL case when

Reply
Contributor
Posts: 62

Proc SQL case when

I have a data set with age and post.

 

Age post

12  AB

13 AB

14 PB

14 PB

Now using case statement in sql need to add age at post level.  The output should be like this

 

Age post tot_age

12  AB  25

13 AB 25

14 PB 28

14 PB 28

 

Super Contributor
Posts: 441

Re: Proc SQL case when

You don't do it with a CASE statement - you need a SUM with GROUP BY i.e.

 

data have;
	length age 8 post $2;
	infile datalines dlm=",";
	input age post;
	datalines;
12,AB
13,AB
14,PB
14,PB
;
run;

proc sql;
	create table want
	as select *, sum(age) as tot_age
	from have
	group by post;
quit;
Contributor
Posts: 62

Re: Proc SQL case when

Posted in reply to ChrisBrooks
Thanks for the reply. Can I do it with case when as well. Need to know that
whether we can sum age based on post and can put under new derived variable
or not.
Super Contributor
Posts: 441

Re: Proc SQL case when

I'm not clear what you mean here - in my example I have created a new derived variable called tot_age. Also a CASE statement will probably be a lot slower than a GROUP BY on a large table?

Super User
Super User
Posts: 7,997

Re: Proc SQL case when

Agree with @ChrisBrooks, case is a conditional operator, what you are talking about is an aggregate.  The two operations are quite different and for different purposes, so whilst it may be possible to do one in the other and vice versa its really not good programming to do that.

Super User
Posts: 7,868

Re: Proc SQL case when


pawandh wrote:
Thanks for the reply. Can I do it with case when as well. Need to know that
whether we can sum age based on post and can put under new derived variable
or not.

Why?

The proper tool for this is a summary function. Don't try to drive in screws with a fork. You'll only hurt yourself and probably accomplish nothing. Use the screwdriver.

 

See Maxim 14.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 441

Re: Proc SQL case when

Posted in reply to KurtBremser

To expand slightly on what @KurtBremser and @RW9 said - I often see on here people saying something like "I have data which looks like this and I need it to look like that using Proc SQL and the [insert statement name] statement". The thing is as a professional programmer the client gives you your input data (point A) and tells you what they want as output data (Point B). The choice of how to get from A to B is entirely up to you and your job is to get there as efficiently as possible without spending 2 weeks trying to figure out how to do it in an suitable way.

 

If a client tells me what technique to use and I don't believe it's suitable I've always told them as politely as possible why my 30 years of doing this for a living leads me to believe that another way is best and I've never yet had a client prove me wrong...

Ask a Question
Discussion stats
  • 6 replies
  • 115 views
  • 2 likes
  • 4 in conversation