DATA Step, Macro, Functions and more

Counting the numbers from a table

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

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. 

 

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!


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

Re: Counting the numbers from a table



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


All Replies
Respected Advisor
Posts: 3,892

Re: Counting the numbers from a table

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: 17,829

Re: Counting the numbers from a table

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: 17,829

Re: Counting the numbers from a table

In SQL you can use:

 

COUNT DISTINCT

 

 

Count (distinct ID) as Unique_ID

Respected Advisor
Posts: 3,892

Re: Counting the numbers from a table

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: 9,681

Re: Counting the numbers from a table



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