SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

proc sql using where or case and when clause

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 146
Accepted Solution

proc sql using where or case and when clause

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.


Accepted Solutions
Solution
2 weeks ago
PROC Star
Posts: 1,547

Re: proc sql using where or case and when clause

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;

View solution in original post


All Replies
Solution
2 weeks ago
PROC Star
Posts: 1,547

Re: proc sql using where or case and when clause

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;
Frequent Contributor
Posts: 146

Re: proc sql using where or case and when clause

Posted in reply to novinosrin

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

PROC Star
Posts: 1,547

Re: proc sql using where or case and when clause

@dr2014 You are welcome. I'm glad Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 196 views
  • 0 likes
  • 2 in conversation