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