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

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. 

 

IDsurvey numberGift_name
1121doll
1121watch
1121wallet
1122doll
2123tv
2123mobile
2122Coffee cup
4124tv
4123wallet
4122watch
4122watch

 

My output should look like below: 

 

Number of surveysNumber of individualsGiftsunique_gifts
1000
2276
3143
4000

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User


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;

View solution in original post

7 REPLIES 7
Patrick
Opal | Level 21

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.

 

danwarags
Obsidian | Level 7

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;

 

Reeza
Super User

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. 

 

danwarags
Obsidian | Level 7
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.
Reeza
Super User

In SQL you can use:

 

COUNT DISTINCT

 

 

Count (distinct ID) as Unique_ID

Patrick
Opal | Level 21

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;
Ksharp
Super User


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;

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
  • 7 replies
  • 1130 views
  • 2 likes
  • 4 in conversation