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!
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;
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.
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;
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.
In SQL you can use:
COUNT DISTINCT
Count (distinct ID) as Unique_ID
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.