DATA Step, Macro, Functions and more

Case when in SQL

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 117
Accepted Solution

Case when in SQL

Hi SAS Experts
Here am trying get the count of names by respndt--there are two scenerios
1.count(names) as completed, by respndt, where completed="Yes"
2.count(names) as total_cont by respndt;

 

plese suggest a way to do this, below is my dataset

 

data abc;
input respndt$ name$ completed$;
datalines;
aa ksv yes
aa kbc no
bb xab no
bb gfg no
bb ghg yes
bb gkg yes
;
run;

 

Thankls & Regards,

Sanjay


Accepted Solutions
Solution
‎05-26-2017 11:58 AM
Super User
Super User
Posts: 7,994

Re: Case when in SQL

Sorry, should have used sum() rather than count():

data abc;
input respndt$ name$ completed$;
datalines;
aa ksv yes
aa kbc no
bb xab no
bb gfg no
bb ghg yes
bb gkg yes
;
run;
proc sql;
  create table WANT as
  select  RESPNDT,
          sum(case when upcase(COMPLETED)="YES" then 1 else 0 end) as COMPLETED,
          count(*) as TOTAL_COUNT
  from    ABC
  group by RESPNDT;
quit;

View solution in original post


All Replies
Super User
Super User
Posts: 7,994

Re: Case when in SQL

Guessing on what you want the output to look like:
 Simplest way is datastep:

data want (keep=sum1 sum2);
  set abc end=last;
  retain sum1 sum2 0;
  sum1=sum1+1;
  sum2=sum2 + ifn(completed="Yes",1,0);
  if last then output;
run;

You can do it in SQL, however you need to provide more information.  SQL count() function is a group by, however you are not grouping by anything here, so the code would be more verbose than the datastep.

Frequent Contributor
Posts: 117

Re: Case when in SQL

Hi RW9,

 

/*Desired output be like this */


respndt Completed total_cont
aa 1 2
bb 2 4

 

even i tried it in sql using case when, but am not much familiar with sql its hard for me to  get the output.

Frequent Contributor
Posts: 117

Re: Case when in SQL

Am grouping it by respndt.

Super User
Posts: 7,863

Re: Case when in SQL

Sort by respndt.

Use by respndt in the data step.

Use first.respndt to reset the counters, and last.respndt to output.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,994

Re: Case when in SQL

Ah, well that simplfies it then:

proc sql;
  create table WANT as
  select  RESPNDT,
          count(case when COMPLETED="Yes" then 1 else 0 end) as COMPLETED,
          count(*) as TOTAL_COUNT
  from    ABC
  group by RESPNDT;
quit;
Frequent Contributor
Posts: 117

Re: Case when in SQL

but my result should be like this

 

respndt Completed total_cont
aa 1 2
bb 2 4

 

 

for respndt "aa" there is completed="yes"---1, and one "yes" and one "no" ---2, which is (respndt=aa Completed=1 total_cont=2)

for respndt "bb" there is completed="yes"---2,and two "yes" and two "no" ---4, which is (respndt=bb Completed=2 total_cont=4)

Solution
‎05-26-2017 11:58 AM
Super User
Super User
Posts: 7,994

Re: Case when in SQL

Sorry, should have used sum() rather than count():

data abc;
input respndt$ name$ completed$;
datalines;
aa ksv yes
aa kbc no
bb xab no
bb gfg no
bb ghg yes
bb gkg yes
;
run;
proc sql;
  create table WANT as
  select  RESPNDT,
          sum(case when upcase(COMPLETED)="YES" then 1 else 0 end) as COMPLETED,
          count(*) as TOTAL_COUNT
  from    ABC
  group by RESPNDT;
quit;
Frequent Contributor
Posts: 117

Re: Case when in SQL

Thank you RW9

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 284 views
  • 0 likes
  • 3 in conversation