topic Re: Case when in SQL in SAS Programming

Case when in SQL

sanjay1 — Fri, 26 May 2017 10:53:47 GMT

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

Re: Case when in SQL

RW9 — Fri, 26 May 2017 11:15:42 GMT

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.

Re: Case when in SQL

sanjay1 — Fri, 26 May 2017 11:25:44 GMT

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.

Re: Case when in SQL

sanjay1 — Fri, 26 May 2017 11:27:49 GMT

Am grouping it by respndt.

Re: Case when in SQL

Kurt_Bremser — Fri, 26 May 2017 11:56:02 GMT

Sort by respndt.

Use by respndt in the data step.

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

Re: Case when in SQL

RW9 — Fri, 26 May 2017 12:04:54 GMT

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;

Re: Case when in SQL

sanjay1 — Fri, 26 May 2017 12:45:09 GMT

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)

Re: Case when in SQL

RW9 — Fri, 26 May 2017 13:10:13 GMT

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;

Re: Case when in SQL

sanjay1 — Fri, 26 May 2017 15:58:14 GMT

Thank you RW9