BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sarobinson010
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

 

View solution in original post

7 REPLIES 7
Reeza
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Barite | Level 11

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
Super User

@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
Tourmaline | Level 20


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;


sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 7 replies
  • 1317 views
  • 1 like
  • 4 in conversation