How do I code the possible combinations of 10 variables into an integer variable?

[sarobinson010]

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

[Reeza]

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

---

[sarobinson010]

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

[Reeza · Accepted solution]

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

---

[sarobinson010]

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

[r_behata]

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;

[Reeza]

@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;

---

[novinosrin]

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;