Hi,
I am trying to count occurrences of a "yes" (coded as 1) across a set of variables.
I have variable names such as "snq_person1_q1, snq_person1_q2,... snq_person6_q10"
I am looking to count the "yes's" for each q10 across these variables per observation. Once I get that, I need to divide that by the number of people they have reported. Each observation can have anywhere between 1 and 6 people that they report on. For example, if someone reported "yes" for each of the 6 people they reported they would have a 6/6. Otherwise, they could report only 5 people and say "yes" to only 3 which would make the total 3/5.
I think the following code is close to counting the number of yes's, but I am not quite there:
data snq;
set RDS;
DrinkWith=count(catx(' ',of snq_person1_q10-snq_person6_q10),'1');
putlog DrinkWith=;
run;
After this, I am not sure how to get how many each of them reported, but obviously after that step it is a simple division DrinkWith/TotalPeople.
Any help would be appreciated and I can provide any other information needed.
Edit: Data below. However, its lengthy due to formats.
data WORK.SNQ;
infile datalines dsd truncover;
input gender:GENDER_. snq_1_age:BEST12. snq_1_relation:SNQ_1_RELATION_. snq_person1_q1:SNQ_PERSON1_Q1_. snq_person1_q2:SNQ_PERSON1_Q2_. snq_person1_q3:SNQ_PERSON1_Q3_. snq_person1_q4:SNQ_PERSON1_Q4_. snq_person1_q5:SNQ_PERSON1_Q5_. snq_person1_q6:SNQ_PERSO
N1_Q6_. snq_person1_q7:SNQ_PERSON1_Q7_. snq_person1_q8:SNQ_PERSON1_Q8_. snq_person1_q9:SNQ_PERSON1_Q9_. snq_person1_q10:SNQ_PERSON1_Q10_. snq_2_age:BEST12. snq_person2_relationship:SNQ_PERSON2_RELATIONSHIP_. snq_person2_q1:SNQ_PERSON2_Q1_. snq_person2_q2:S
NQ_PERSON2_Q2_. snq_person2_q3:SNQ_PERSON2_Q3_. snq_person2_q4:SNQ_PERSON2_Q4_. snq_person2_q5:SNQ_PERSON2_Q5_. snq_person2_q6:SNQ_PERSON2_Q6_. snq_person2_q7:SNQ_PERSON2_Q7_. snq_person2_q8:SNQ_PERSON2_Q8_. snq_person2_q9:SNQ_PERSON2_Q9_. snq_person2_q10:
SNQ_PERSON2_Q10_. snq_3_age:BEST12. snq_person3_relationship:SNQ_PERSON3_RELATIONSHIP_. snq_person3_q1:SNQ_PERSON3_Q1_. snq_person3_q2:SNQ_PERSON3_Q2_. snq_person3_q3:SNQ_PERSON3_Q3_. snq_person3_q4:SNQ_PERSON3_Q4_. snq_person3_q5:SNQ_PERSON3_Q5_. snq_pers
3 The SAS System 10:55 Thursday, March 21, 2019
on3_q6:SNQ_PERSON3_Q6_. snq_person3_q7:SNQ_PERSON3_Q7_. snq_person3_q8:SNQ_PERSON3_Q8_. snq_person3_q9:SNQ_PERSON3_Q9_. snq_person3_q10:SNQ_PERSON3_Q10_. snq_4_age:BEST12. snq_person4_relationship:SNQ_PERSON4_RELATIONSHIP_. snq_person4_q1:SNQ_PERSON4_Q1_.
snq_person4_q2:SNQ_PERSON4_Q2_. snq_person4_q3:SNQ_PERSON4_Q3_. snq_person4_q4:SNQ_PERSON4_Q4_. snq_person4_q5:SNQ_PERSON4_Q5_. snq_person4_q6:SNQ_PERSON4_Q6_. snq_person4_q7:SNQ_PERSON4_Q7_. snq_person4_q8:SNQ_PERSON4_Q8_. snq_person4_q9:SNQ_PERSON4_Q9_.
snq_person4_q10:SNQ_PERSON4_Q10_. snq_5_age:BEST12. snq_person5_relationship:SNQ_PERSON5_RELATIONSHIP_. snq_person5_q1:SNQ_PERSON5_Q1_. snq_person5_q2:SNQ_PERSON5_Q2_. snq_person5_q3:SNQ_PERSON5_Q3_. snq_person5_q4:SNQ_PERSON5_Q4_. snq_person5_q5:SNQ_PERSO
N5_Q5_. snq_person5_q6:SNQ_PERSON5_Q6_. snq_person5_q7:SNQ_PERSON5_Q7_. snq_person5_q8:SNQ_PERSON5_Q8_. snq_person5_q9:SNQ_PERSON5_Q9_. snq_person5_q10:SNQ_PERSON5_Q10_. snq_6_age:BEST12. snq_person6_relationship:SNQ_PERSON6_RELATIONSHIP_. snq_person6_q1:S
NQ_PERSON6_Q1_. snq_person6_q2:SNQ_PERSON6_Q2_. snq_person6_q3:SNQ_PERSON6_Q3_. snq_person6_q4:SNQ_PERSON6_Q4_. snq_person6_q5:SNQ_PERSON6_Q5_. snq_person6_q6:SNQ_PERSON6_Q6_. snq_person6_q7:SNQ_PERSON6_Q7_. snq_person6_q8:SNQ_PERSON6_Q8_. snq_person6_q9:S
NQ_PERSON6_Q9_. snq_person6_q10:SNQ_PERSON6_Q10_. snq_7_age:BEST12. snq_person7_relationship:SNQ_PERSON7_RELATIONSHIP_. snq_person7_q1:SNQ_PERSON7_Q1_. snq_person7_q2:SNQ_PERSON7_Q2_. snq_person7_q3:SNQ_PERSON7_Q3_. snq_person7_q4:SNQ_PERSON7_Q4_. snq_pers
on7_q5:SNQ_PERSON7_Q5_. snq_person7_q6:SNQ_PERSON7_Q6_. snq_person7_q7:SNQ_PERSON7_Q7_. snq_person7_q8:SNQ_PERSON7_Q8_. snq_person7_q9:SNQ_PERSON7_Q9_. snq_person7_q10:SNQ_PERSON7_Q10_. snq_8_age:BEST12. snq_person8_relationship:SNQ_PERSON8_RELATIONSHIP_.
snq_person8_q1:SNQ_PERSON8_Q1_. snq_person8_q2:SNQ_PERSON8_Q2_. snq_person8_q3:SNQ_PERSON8_Q3_. snq_person8_q4:SNQ_PERSON8_Q4_. snq_person8_q5:SNQ_PERSON8_Q5_. snq_person8_q6:SNQ_PERSON8_Q6_. snq_person8_q7:SNQ_PERSON8_Q7_. snq_person8_q8:SNQ_PERSON8_Q8_.
snq_person8_q09:SNQ_PERSON8_Q09_. snq_person8_q10:SNQ_PERSON8_Q10_. snq_9_age:BEST12. snq_person9_relationship:SNQ_PERSON9_RELATIONSHIP_. snq_person9_q1:SNQ_PERSON9_Q1_. snq_person9_q2:SNQ_PERSON9_Q2_. snq_person9_q3:SNQ_PERSON9_Q3_. snq_person9_q4:SNQ_PER
SON9_Q4_. snq_person9_q5:SNQ_PERSON9_Q5_. snq_person9_q6:SNQ_PERSON9_Q6_. snq_person9_q7:SNQ_PERSON9_Q7_. snq_person9_q8:SNQ_PERSON9_Q8_. snq_person9_q9:SNQ_PERSON9_Q9_. snq_person9_q10:SNQ_PERSON9_Q10_. snq_10_age:BEST12. snq_person10_relationship:SNQ_PER
SON10_RELATIONSHIP_. snq_person10_q1:SNQ_PERSON10_Q1_. snq_person10_q2:SNQ_PERSON10_Q2_. snq_person10_q3:SNQ_PERSON10_Q3_. snq_person10_q4:SNQ_PERSON10_Q4_. snq_person10_q5:SNQ_PERSON10_Q5_. snq_person10_q6:SNQ_PERSON10_Q6_. snq_person10_q7:SNQ_PERSON10_Q7
_. snq_person10_q8:SNQ_PERSON10_Q8_. snq_person10_q9:SNQ_PERSON10_Q9_. snq_person10_q10:SNQ_PERSON10_Q10_.;
format gender GENDER_. snq_1_age BEST12. snq_1_relation SNQ_1_RELATION_. snq_person1_q1 SNQ_PERSON1_Q1_. snq_person1_q2 SNQ_PERSON1_Q2_. snq_person1_q3 SNQ_PERSON1_Q3_. snq_person1_q4 SNQ_PERSON1_Q4_. snq_person1_q5 SNQ_PERSON1_Q5_. snq_person1_q6 SNQ_PERS
ON1_Q6_. snq_person1_q7 SNQ_PERSON1_Q7_. snq_person1_q8 SNQ_PERSON1_Q8_. snq_person1_q9 SNQ_PERSON1_Q9_. snq_person1_q10 SNQ_PERSON1_Q10_. snq_2_age BEST12. snq_person2_relationship SNQ_PERSON2_RELATIONSHIP_. snq_person2_q1 SNQ_PERSON2_Q1_. snq_person2_q2
SNQ_PERSON2_Q2_. snq_person2_q3 SNQ_PERSON2_Q3_. snq_person2_q4 SNQ_PERSON2_Q4_. snq_person2_q5 SNQ_PERSON2_Q5_. snq_person2_q6 SNQ_PERSON2_Q6_. snq_person2_q7 SNQ_PERSON2_Q7_. snq_person2_q8 SNQ_PERSON2_Q8_. snq_person2_q9 SNQ_PERSON2_Q9_. snq_person2_q10
SNQ_PERSON2_Q10_. snq_3_age BEST12. snq_person3_relationship SNQ_PERSON3_RELATIONSHIP_. snq_person3_q1 SNQ_PERSON3_Q1_. snq_person3_q2 SNQ_PERSON3_Q2_. snq_person3_q3 SNQ_PERSON3_Q3_. snq_person3_q4 SNQ_PERSON3_Q4_. snq_person3_q5 SNQ_PERSON3_Q5_. snq_per
son3_q6 SNQ_PERSON3_Q6_. snq_person3_q7 SNQ_PERSON3_Q7_. snq_person3_q8 SNQ_PERSON3_Q8_. snq_person3_q9 SNQ_PERSON3_Q9_. snq_person3_q10 SNQ_PERSON3_Q10_. snq_4_age BEST12. snq_person4_relationship SNQ_PERSON4_RELATIONSHIP_. snq_person4_q1 SNQ_PERSON4_Q1_.
snq_person4_q2 SNQ_PERSON4_Q2_. snq_person4_q3 SNQ_PERSON4_Q3_. snq_person4_q4 SNQ_PERSON4_Q4_. snq_person4_q5 SNQ_PERSON4_Q5_. snq_person4_q6 SNQ_PERSON4_Q6_. snq_person4_q7 SNQ_PERSON4_Q7_. snq_person4_q8 SNQ_PERSON4_Q8_. snq_person4_q9 SNQ_PERSON4_Q9_.
snq_person4_q10 SNQ_PERSON4_Q10_. snq_5_age BEST12. snq_person5_relationship SNQ_PERSON5_RELATIONSHIP_. snq_person5_q1 SNQ_PERSON5_Q1_. snq_person5_q2 SNQ_PERSON5_Q2_. snq_person5_q3 SNQ_PERSON5_Q3_. snq_person5_q4 SNQ_PERSON5_Q4_. snq_person5_q5 SNQ_PERS
ON5_Q5_. snq_person5_q6 SNQ_PERSON5_Q6_. snq_person5_q7 SNQ_PERSON5_Q7_. snq_person5_q8 SNQ_PERSON5_Q8_. snq_person5_q9 SNQ_PERSON5_Q9_. snq_person5_q10 SNQ_PERSON5_Q10_. snq_6_age BEST12. snq_person6_relationship SNQ_PERSON6_RELATIONSHIP_. snq_person6_q1
SNQ_PERSON6_Q1_. snq_person6_q2 SNQ_PERSON6_Q2_. snq_person6_q3 SNQ_PERSON6_Q3_. snq_person6_q4 SNQ_PERSON6_Q4_. snq_person6_q5 SNQ_PERSON6_Q5_. snq_person6_q6 SNQ_PERSON6_Q6_. snq_person6_q7 SNQ_PERSON6_Q7_. snq_person6_q8 SNQ_PERSON6_Q8_. snq_person6_q9
SNQ_PERSON6_Q9_. snq_person6_q10 SNQ_PERSON6_Q10_. snq_7_age BEST12. snq_person7_relationship SNQ_PERSON7_RELATIONSHIP_. snq_person7_q1 SNQ_PERSON7_Q1_. snq_person7_q2 SNQ_PERSON7_Q2_. snq_person7_q3 SNQ_PERSON7_Q3_. snq_person7_q4 SNQ_PERSON7_Q4_. snq_per
son7_q5 SNQ_PERSON7_Q5_. snq_person7_q6 SNQ_PERSON7_Q6_. snq_person7_q7 SNQ_PERSON7_Q7_. snq_person7_q8 SNQ_PERSON7_Q8_. snq_person7_q9 SNQ_PERSON7_Q9_. snq_person7_q10 SNQ_PERSON7_Q10_. snq_8_age BEST12. snq_person8_relationship SNQ_PERSON8_RELATIONSHIP_.
snq_person8_q1 SNQ_PERSON8_Q1_. snq_person8_q2 SNQ_PERSON8_Q2_. snq_person8_q3 SNQ_PERSON8_Q3_. snq_person8_q4 SNQ_PERSON8_Q4_. snq_person8_q5 SNQ_PERSON8_Q5_. snq_person8_q6 SNQ_PERSON8_Q6_. snq_person8_q7 SNQ_PERSON8_Q7_. snq_person8_q8 SNQ_PERSON8_Q8_.
snq_person8_q09 SNQ_PERSON8_Q09_. snq_person8_q10 SNQ_PERSON8_Q10_. snq_9_age BEST12. snq_person9_relationship SNQ_PERSON9_RELATIONSHIP_. snq_person9_q1 SNQ_PERSON9_Q1_. snq_person9_q2 SNQ_PERSON9_Q2_. snq_person9_q3 SNQ_PERSON9_Q3_. snq_person9_q4 SNQ_PE
RSON9_Q4_. snq_person9_q5 SNQ_PERSON9_Q5_. snq_person9_q6 SNQ_PERSON9_Q6_. snq_person9_q7 SNQ_PERSON9_Q7_. snq_person9_q8 SNQ_PERSON9_Q8_. snq_person9_q9 SNQ_PERSON9_Q9_. snq_person9_q10 SNQ_PERSON9_Q10_. snq_10_age BEST12. snq_person10_relationship SNQ_PE
RSON10_RELATIONSHIP_. snq_person10_q1 SNQ_PERSON10_Q1_. snq_person10_q2 SNQ_PERSON10_Q2_. snq_person10_q3 SNQ_PERSON10_Q3_. snq_person10_q4 SNQ_PERSON10_Q4_. snq_person10_q5 SNQ_PERSON10_Q5_. snq_person10_q6 SNQ_PERSON10_Q6_. snq_person10_q7 SNQ_PERSON10_Q
7_. snq_person10_q8 SNQ_PERSON10_Q8_. snq_person10_q9 SNQ_PERSON10_Q9_. snq_person10_q10 SNQ_PERSON10_Q10_.;
label gender="Female =0, Male =1";
datalines;
Male 26 Spouse or partner 5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 1: Not at all 1: Not at all 1: Not at all Yes 26 Friend 4: Quite a bit 4: Quite a bit 4: Quite a bit 4: Quite a bit 5: A great deal
3: Moderately 4: Quite a bit 1: Not at all 1: Not at all Yes 25 Friend 5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 3: Moderately 1: Not at all 1: Not at all Yes 25 Friend 5: A great deal 5: A great deal
5: A great deal 5: A great deal 5: A great deal 3: Moderately 5: A great deal 1: Not at all 1: Not at all Yes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Female 26 Friend 5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 3: Moderately 2: A little 1: Not at all 2: A little Yes 29 Spouse or partner 4: Quite a bit 4: Quite a bit 4: Quite a bit 5: A great deal 5: A great deal
4: Quite a bit 4: Quite a bit 2: A little 2: A little Yes 61 Other family member or relative 5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 2: A little 1: Not at all 1: Not at all No 27 Friend
5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 4: Quite a bit 3: Moderately 1: Not at all 1: Not at all Yes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . .
Male 23 Friend 4: Quite a bit 4: Quite a bit 5: A great deal 4: Quite a bit 4: Quite a bit 4: Quite a bit 3: Moderately 1: Not at all 2: A little Yes 25 Friend 4: Quite a bit 4: Quite a bit 5: A great deal 3: Moderately 5: A great deal 4: Quite a bit
3: Moderately 3: Moderately 2: A little Yes 23 Friend 3: Moderately 3: Moderately 4: Quite a bit 3: Moderately 5: A great deal 5: A great deal 2: A little 2: A little 1: Not at all Yes 26 Friend 2: A little 3: Moderately 2: A little 3: Moderately
5: A great deal 4: Quite a bit 2: A little 2: A little 1: Not at all Yes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Male 57 Other family member or relative 5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 1: Not at all 2: A little 1: Not at all Yes 22 Friend 5: A great deal 5: A great deal 5: A great deal 5: A great deal
5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal Yes 23 Friend 5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 1: Not at all 4: Quite a bit 4: Quite a bit Yes 23 Friend
5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 5: A great deal 2: A little 5: A great deal Yes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . .
Male 25 Friend 4: Quite a bit 4: Quite a bit 3: Moderately 3: Moderately 5: A great deal 4: Quite a bit 3: Moderately 1: Not at all 2: A little Yes 26 Friend 4: Quite a bit 4: Quite a bit 4: Quite a bit 3: Moderately 5: A great deal 4: Quite a bit
2: A little 2: A little 2: A little Yes 23 Other family member or relative 4: Quite a bit 4: Quite a bit 4: Quite a bit 4: Quite a bit 4: Quite a bit 4: Quite a bit 1: Not at all 1: Not at all 1: Not at all No 25 Work or school associate 3: Moderately
3: Moderately 3: Moderately 3: Moderately 4: Quite a bit 3: Moderately 2: A little 1: Not at all 2: A little Yes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
;;;;
NOTE: There were 5 observations read from the data set WORK.SNQ.
NOTE: DATA statement used (Total process time):
real time 0.11 seconds
cpu time 0.10 seconds
"nce I get that, I need to divide that by the number of people they have reported."
Number of people= number of obs assumping each ob is one unique individual's report???
This point is not clear to me leading to assumptions. Typically no data samples leads to assumptions
I updated the original to reflect that this is survey data. Each observation can have between 1-6 people who they reported on. I only am interested in question 10-- snq_person1_q10, snq_person2_q10, ...etc-- where the amount of "yes" on q10 is put over the total amount of people they reported.
I also included my data, but it is lengthy due to formats.
If your data value has only two values, yes and no, with possibly missing then coding them as character yes and no in any form adds lots of work. If you code values as numeric with 1 for yes and 0 for no then you can
sum variables to the number of yes
take the mean to get a percentage of yes
use the N function to return how many actually have a value and a number of other common questions about a group of dichotomous variables:
proc format library=work; invalue yn (upcase) 'Y'=1 'N'=0 ; run; data example ; informat q1_1-q1_10 yn.; input q1_1-q1_10 ; datalines; y y n n . y y y n n y y y y y y y y y y n n n n n n n n n n n . . . . . . . . . . . . . . . . . . . . . . . y . . . . . ; run; data want; set example; numyes = sum( of q1_:); pctyes = mean( of q1_:); /* option multiply by 100 for "nice" %*/ numanswered = n( of q1_1:); anyyes = (max( of q1_:) =1); anyno = (min( of q1_:) =0); allanswerssame = (range( of q1_:) =0); anyanswerdif = (range( of q1_:) >0); run;
Note the behavior of the range function for all missing values.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.