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