10-05-2017 03:01 AM
I have a data set with age and post.
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
10-05-2017 03:27 AM
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;
10-05-2017 03:39 AM
10-05-2017 03:43 AM
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?
10-05-2017 04:28 AM
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.
10-05-2017 04:51 AM
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
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.
10-05-2017 07:23 AM
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...