topic Re: How do I code the possible combinations of 10 variables into an integer variable? in New SAS User

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

sarobinson010 — Mon, 19 Nov 2018 22:10:34 GMT

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

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

Reeza — Mon, 19 Nov 2018 22:13:42 GMT

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

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

sarobinson010 — Mon, 19 Nov 2018 22:36:05 GMT

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

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

Reeza — Mon, 19 Nov 2018 22:42:33 GMT

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

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

r_behata — Mon, 19 Nov 2018 23:00:56 GMT

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;

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

Reeza — Mon, 19 Nov 2018 23:17:14 GMT

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

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

sarobinson010 — Mon, 19 Nov 2018 23:47:45 GMT

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

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

novinosrin — Tue, 20 Nov 2018 04:03:43 GMT



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;