BookmarkSubscribeRSS Feed
pawandh
Fluorite | Level 6

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

 

6 REPLIES 6
ChrisBrooks
Ammonite | Level 13

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;
pawandh
Fluorite | Level 6
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.
ChrisBrooks
Ammonite | Level 13

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

@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.

ChrisBrooks
Ammonite | Level 13

To expand slightly on what @Kurt_Bremser 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...

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 988 views
  • 2 likes
  • 4 in conversation