Case when in SQL

[sanjay1]

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

[RW9]

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.

[sanjay1]

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.

[sanjay1]

Am grouping it by respndt.

[Kurt_Bremser]

Sort by respndt.

Use by respndt in the data step.

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

[RW9]

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;

[sanjay1]

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)

[RW9 · Accepted solution]

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;

[sanjay1]

Thank you RW9