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
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;
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?
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.
@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.
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...
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.