Dear SAS users,
I am using SAS Enterprise Guide to look at use data for an online patient portal. i have counts of use per quarter over 2.5 years (10 quarters). I would like to code this data into an integer that represents how many quarters a patient used the portal at least once. For example, if a patient used the portal at least once for 7 out of the 10 quarters, I would want to code this as a 7, or if the patient only used this portal once for 3 out of the 10 quarters, this would be coded as a 3.
With 10 quarters of data, the amount of combinations possible is exhausting and I'm looking for a code that can automate this process.
My dataset looks similar to this:
ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 1 3 4 5 0 0 0 3 4 1 0 2 1 1 0 0 0 0 2 6 1 4 3 1 1 1 1 0 0 0 7 1 0
So far, I have been able to code for all possible combinations of quarters with 1 or more counts using the code below. However, this does not allow me to code the possible combinations as integers.
data work.sList; length sList$200; array _s Q1 -- Q10; do i=1 to dim(_sum); if _s(i) > 0 then sList=catx (" and ", sList, vname (_s{i})); end; keep sList ID; run; proc sql; create table sCounts as select sList, count(*) as nbPat from sList group by sList; quit;
Your help is greatly appreciated.
Best,
Stephanie
Ok. I think coding to 0/1 would be helpful here. Then you can use the SUM function.
You didn't have a SET statement, you most likely do need one.
Check the WANT and WANT_SUMMARY output data sets as well - they'll be in the work library.
data work.sList;
SET <Source data>;
array _Quest(*) Q1 - Q10;
array _d(*) d1-d10;
sus_use=0;
do i=1 to dim(_Quest);
if _quest(i) > 0 then _d(i)=1;
else _d(i)=0;
end;
sus_use = sum(of D1-D10);
keep ID sus_use;
run;
proc freq data=sList noprint;
table D1*D2*D3*D4*D5*D6*D7*D8*D9*D10 / out=want list;
table sus_use / out=want_Summary;
run;
@sarobinson010 wrote:
Dear Reeza,
Thank you for your reply. Yes, based on the example data I posted, I would want to code for a new variable to represent "sustained use" (sus_use), as in this example below. I hope that clarification is helpful.
Thank you,
Stephanie
ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Sus_use 1 3 4 5 0 0 0 3 4 1 0 6 2 1 1 0 0 0 0 2 6 1 4 6 3 1 1 1 1 0 0 0 7 1 0 6
So what do you expect as output for that input?
Would it be 6, 6, 6?
Or are you looking for something else?
One way is to convert them all to 0/1s and then use PROC FREQ to summarize it with the LIST option.
Once you clarify what you need as output, I can provide the appropriate code for the specific option.
@sarobinson010 wrote:
Dear SAS users,
I am using SAS Enterprise Guide to look at use data for an online patient portal. i have counts of use per quarter over 2.5 years (10 quarters). I would like to code this data into an integer that represents how many quarters a patient used the portal at least once. For example, if a patient used the portal at least once for 7 out of the 10 quarters, I would want to code this as a 7, or if the patient only used this portal once for 3 out of the 10 quarters, this would be coded as a 3.
With 10 quarters of data, the amount of combinations possible is exhausting and I'm looking for a code that can automate this process.
My dataset looks similar to this:
ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 1 3 4 5 0 0 0 3 4 1 0 2 1 1 0 0 0 0 2 6 1 4 3 1 1 1 1 0 0 0 7 1 0
So far, I have been able to code for all possible combinations of quarters with 1 or more counts using the code below. However, this does not allow me to code the possible combinations as integers.
data work.sList; length sList$200; array _s Q1 -- Q10; do i=1 to dim(_sum); if _s(i) > 0 then sList=catx (" and ", sList, vname (_s{i})); end; keep sList ID; run; proc sql; create table sCounts as select sList, count(*) as nbPat from sList group by sList; quit;Your help is greatly appreciated.
Best,
Stephanie
Dear Reeza,
Thank you for your reply. Yes, based on the example data I posted, I would want to code for a new variable to represent "sustained use" (sus_use), as in this example below. I hope that clarification is helpful.
Thank you,
Stephanie
ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Sus_use 1 3 4 5 0 0 0 3 4 1 0 6 2 1 1 0 0 0 0 2 6 1 4 6 3 1 1 1 1 0 0 0 7 1 0 6
Ok. I think coding to 0/1 would be helpful here. Then you can use the SUM function.
You didn't have a SET statement, you most likely do need one.
Check the WANT and WANT_SUMMARY output data sets as well - they'll be in the work library.
data work.sList;
SET <Source data>;
array _Quest(*) Q1 - Q10;
array _d(*) d1-d10;
sus_use=0;
do i=1 to dim(_Quest);
if _quest(i) > 0 then _d(i)=1;
else _d(i)=0;
end;
sus_use = sum(of D1-D10);
keep ID sus_use;
run;
proc freq data=sList noprint;
table D1*D2*D3*D4*D5*D6*D7*D8*D9*D10 / out=want list;
table sus_use / out=want_Summary;
run;
@sarobinson010 wrote:
Dear Reeza,
Thank you for your reply. Yes, based on the example data I posted, I would want to code for a new variable to represent "sustained use" (sus_use), as in this example below. I hope that clarification is helpful.
Thank you,
Stephanie
ID Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Sus_use 1 3 4 5 0 0 0 3 4 1 0 6 2 1 1 0 0 0 0 2 6 1 4 6 3 1 1 1 1 0 0 0 7 1 0 6
This is exactly what I was looking for, thank you so much. Making each quarter dichotomous (0 or 1) and the summing across the quarters in hindsight seems like such a simple fix.
You've been a great help. Thank you.
Stephanie
Is this what you are looking for ?
data have;
input ID $ Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10;
cards;
1 3 4 5 0 0 0 3 4 1 0
2 1 1 0 0 0 0 2 6 1 4
3 1 1 1 1 0 0 0 7 1 0
;
run;
data want;
set have;
array qtr q1-q10;
Sus_use=0;
do over qtr;
if qtr > 0 then Sus_use +1;
end;
run;
@r_behata DO OVER is deprecated so it's better to not use it in new programs. Backwards compatibility is maintained at the moment, but that could change.
@r_behata wrote:
Is this what you are looking for ?
data have; input ID $ Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10; cards; 1 3 4 5 0 0 0 3 4 1 0 2 1 1 0 0 0 0 2 6 1 4 3 1 1 1 1 0 0 0 7 1 0 ; run; data want; set have; array qtr q1-q10; Sus_use=0; do over qtr; if qtr > 0 then Sus_use +1; end; run;
data have;
input ID $ Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10;
cards;
1 3 4 5 0 0 0 3 4 1 0
2 1 1 0 0 0 0 2 6 1 4
3 1 1 1 1 0 0 0 7 1 0
;
run;
data want;
set have;
_t = put (0, rb8.) ;
array t(*) q:;
sus_use = dim(t)-count(put(peekclong (addrlong(t[1]), 80), $80.),_t) ;
drop _t;
run;
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!
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.