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.
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;
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;
@novinosrinThanks for your solution. It worked and was very helpful.
@dr2014 You are welcome. I'm glad 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.