proc sql using where or case and when clause

[dr2014]

Hi all,

I need some help in summarizing data using proc sql and/or any other way I could accomplish this. Below is the dataset 'have'

id	var1	var2	attempts
001	0	1	1
001	1	1	2
001	1	1	3
002	1	1	1
002	1	1	2
002	0	1	3
003	1	1	1
004	1	1	1
005	1	1	1
006	1	1	1
007	1	0	1

 I need to have counts of 'var2' based on values in 'var1 and var2' and also total count of 'attempts' by each id.

 So basically I need the dataset 'want' as I copy pasted below:

id	var2_novar1	var2_var1	novar2_var1	tot_attempts
001	1	2	0	3
002	1	2	0	3
003	0	1	0	1
004	0	1	0	1
005	0	1	0	1
006	0	1	0	1
007	0	0	1	1

var2_novar1:  counts where var2=1 and var1=0 by unique id

var2_var1:       counts where var2=1 and var1=1 by unique id

novar2_var1:  counts where var2=0 and var1=1 by unique id

tot_attempts: total count of attempts by unique id i.e. if there are 3 attempts for an id then tot_attempts=3

I used the below code and was able to get the variables one at a time i.evar2_novar1, var2_var1, novar2_var1 respectively and tot_attempts.

proc sql;

create table want as

select distinct id, count(var2) as var2_novar1, count(attempts)

from have

where var1=0 and var2=1

group by id;

quit;

Is there a way I can get all variables by writing 1 sas program? I don't want to use macros to get 3 different datasets and then merge.

I tried using the code below to create 1 variable but it didn't work. If it worked I was looking to use it in getting the rest of the variables in the same program. It refers to a syntax error in the 'case & when clause' and I am unable to figure it out.

proc sql;

create table want as

select distinct id, count(case var2 when (var1=0 and var2=1)) as var2_novar1,

               count(attempts)

from have

group by id;

quit;

Could anyone please help me with this? Thanks.

[novinosrin · Accepted solution]

data have;
input id	var1	var2	attempts;
cards;
1	0	1	1
1	1	1	2
1	1	1	3
2	1	1	1
2	1	1	2
2	0	1	3
3	1	1	1
4	1	1	1
5	1	1	1
6	1	1	1
7	1	0	1
;
proc sql;
create table want as
select *,sum(var2=1 and var1=0) as var2_novar1, sum(var2=1 and var1=1) as var2_var1, sum(var2=0 and var1=1) as novar2_var1
from have
group by id
having attempts=max(attempts);
quit;

[dr2014]

@novinosrinThanks for your solution. It worked and was very helpful.

[novinosrin]

@dr2014 You are welcome. I'm glad 🙂