Solved
Contributor
Posts: 44

Counting the numbers from a table

Hello Everyone,

I have a data set showing the number of Individuals who participated in different surveys. I want to know how many individuals participated in one survey, how many participated in 2 surveys, 3 surveys etc. I am also interested in knowing the number of gifts these individuals received.

 ID survey number Gift_name 1 121 doll 1 121 watch 1 121 wallet 1 122 doll 2 123 tv 2 123 mobile 2 122 Coffee cup 4 124 tv 4 123 wallet 4 122 watch 4 122 watch

My output should look like below:

 Number of surveys Number of individuals Gifts unique_gifts 1 0 0 0 2 2 7 6 3 1 4 3 4 0 0 0

In the above table all the individuals participated in either 2 or 3 surveys. There were 2 individuals who participated in 2 surveys (ID- 1,2) and there was only one individual (ID-4) who participated in 3 surveys. The gifts should be summed up as shown in the output table (For individuals who participated in 2 surveys the gifts are summed up as: (4+3)=7. The unique gifts would be 6 (Because ID-1 got watch two times).

Thank you!

Accepted Solutions
Solution
‎10-07-2016 03:12 PM
Super User
Posts: 10,860

Re: Counting the numbers from a table

Posted in reply to danwarags
```

data have;
infile cards expandtabs truncover;
input ID	survey	Gift_name \$;
cards;
1	121	doll
1	121	watch
1	121	wallet
1	122	doll
2	123	tv
2	123	mobile
2	122	Coffee cup
4	124	tv
4	123	wallet
4	122	watch
4	122	watch
;
run;

proc sql;
create table temp as
select id,count(distinct survey) as n_survey,
count(*) as gift,
count(distinct gift_name) as n_gift
from have
group by id;

create table want as
select n_survey,sum(gift) as gift ,sum(n_gift) as n_gift
from temp
group by n_survey;
quit;
```

All Replies
Respected Advisor
Posts: 4,802

Re: Counting the numbers from a table

Posted in reply to danwarags

Please provide a data step which generates your "have" data.

What have you done so far? Please share the not yet working code you've developed already, explain us where you got stuck and we can take it from there.

Contributor
Posts: 44

Re: Counting the numbers from a table

Thanks Patrick. I posted a similar question before. But, for that I did not have any duplicate values for the table statement used in proc freq. This question has duplicate values within each individual.  Below is the code I used.

``````data have;
input id survey_number gift_name\$;
datalines;
1	121	doll
1	121	watch
1	121	wallet
1	122	doll
2	123	tv
2	123	mobile
2	122	Coffee cup
4	124	tv
4	123	wallet
4	122	watch
4	122	watch
;

proc freq data= have noprint;
table survey_number/out=countID;
run;

proc freq data=countID;
table count/out=Want;
run;``````

Super User
Posts: 24,026

Re: Counting the numbers from a table

Posted in reply to danwarags

This is incredibly similar to your previous question here.

https://communities.sas.com/t5/Base-SAS-Programming/Calculating-average-age/m-p/301952#M63979

Although we're happy to help, you should be attempting some of this yourself.

Contributor
Posts: 44

Re: Counting the numbers from a table

I totally agree that the question was similar to the one posted by me before. But the difficult part is the table used before had unique values in the proc freq table statement. This time there are duplicate values for survey_number. This was little tricky for me.
Super User
Posts: 24,026

Re: Counting the numbers from a table

Posted in reply to danwarags

In SQL you can use:

COUNT DISTINCT

Count (distinct ID) as Unique_ID

Respected Advisor
Posts: 4,802

Re: Counting the numbers from a table

Posted in reply to danwarags

You really need to start posting part of your own code/work first as else it feels you're asking us to do your job.

``````data have;
input id survey_number gift_name\$;
datalines;
1 121 doll
1 121 watch
1 121 wallet
1 122 doll
2 123 tv
2 123 mobile
2 122 Coffee cup
4 124 tv
4 123 wallet
4 122 watch
4 122 watch
;

proc sql;
/* step 1: counts per id */
create view v_inner as
select
id,
count(distinct survey_number) as n_surveys_per_individual,
count(gift_name) as n_gifts,
count(distinct gift_name) as n_distinct_gift
from have
group by id
;

/* step 2: count/sum of result from 1 to group by "n surveys per individual" */
create table want1 as
select
n_surveys_per_individual as number_of_surveys,
count(id) as number_of_individuals,
sum(n_gifts) as n_gifts,
sum(n_distinct_gift) as n_distinct_gift_per_individual
from v_inner
group by n_surveys_per_individual
;
quit;

/* logically same as above but using an inline view instead of an explicit view */
proc sql;
create table want2 as
select
n_surveys_per_individual as number_of_surveys,
count(id) as number_of_individuals,
sum(n_gifts) as n_gifts,
sum(n_distinct_gift) as n_distinct_gift_per_individual
from
(
select
id,
count(distinct survey_number) as n_surveys_per_individual,
count(gift_name) as n_gifts,
count(distinct gift_name) as n_distinct_gift
from have
group by id
)
group by n_surveys_per_individual
;
quit;
``````
Solution
‎10-07-2016 03:12 PM
Super User
Posts: 10,860

Re: Counting the numbers from a table

Posted in reply to danwarags
```

data have;
infile cards expandtabs truncover;
input ID	survey	Gift_name \$;
cards;
1	121	doll
1	121	watch
1	121	wallet
1	122	doll
2	123	tv
2	123	mobile
2	122	Coffee cup
4	124	tv
4	123	wallet
4	122	watch
4	122	watch
;
run;

proc sql;
create table temp as
select id,count(distinct survey) as n_survey,
count(*) as gift,
count(distinct gift_name) as n_gift
from have
group by id;

create table want as
select n_survey,sum(gift) as gift ,sum(n_gift) as n_gift
from temp
group by n_survey;
quit;
```
☑ This topic is solved.

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

Discussion stats
• 7 replies
• 408 views
• 2 likes
• 4 in conversation