BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sanjay1
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Obsidian | Level 7

Am grouping it by respndt.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

Thank you RW9

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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