topic Re: making a summarizing table from repeatedly measured data over time in SAS Programming

making a summarizing table from repeatedly measured data over time

asinusdk — Tue, 27 Nov 2018 17:04:56 GMT

In a longitudinal data, the frequency can be calculated by proc freq, but what if I want to know whether a subject has a particular response or not over the time in a long format. I think it will be necessary to have a summarized table.

For example, I have a long format data like below.

time	id	v1
1	1	no
2	1	no
3	1	yes
4	1	yes
1	2	no
2	2	no
3	2	no
4	2	no
1	3	yes
2	3	no
3	3	no
4	3	no

from this table, I want to know how many subjects (id) have at least one response (yes) for v1 over time.

In this case v1 is shown in id 1 and id 3 so that will be 2. Is it necessary to have a table like below to obtain the results? If it's the case, could you please let me know the programming code? Thanks.

id	total_v1	v1_freq
1	yes	2
2	no	0
3	yes	1

Re: making a summarizing table from repeatedly measured data over time

Reeza — Tue, 27 Nov 2018 17:08:14 GMT

You can build on the approach used here.
https://communities.sas.com/t5/SAS-Programming/Vertical-joining/m-p/504927

Re: making a summarizing table from repeatedly measured data over time

novinosrin — Tue, 27 Nov 2018 17:12:43 GMT

data have;
input time	id	v1 $;
cards;
1	1	no
2	1	no
3	1	yes
4	1	yes
1	2	no
2	2	no
3	2	no
4	2	no
1	3	yes
2	3	no
3	3	no
4	3	no
;

data want;
do v1_freq=1 by 1  until(last.v1);
set have;
by id v1 notsorted;
end;
v1_freq=ifn(v1='yes',v1_freq,0);
keep id v1:;
run;

Re: making a summarizing table from repeatedly measured data over time

asinusdk — Tue, 27 Nov 2018 17:32:23 GMT

Thanks so much.

Re: making a summarizing table from repeatedly measured data over time

asinusdk — Tue, 27 Nov 2018 19:02:31 GMT

Thanks 🙂

Re: making a summarizing table from repeatedly measured data over time

asinusdk — Tue, 27 Nov 2018 21:06:38 GMT

I tried the code and got the result like this:

id	total_v1	v1_freq
1	yes	2
1	no	0
2	no	0
3	yes	1
3	no	0

Rather, what I wanted looks like this.

id	total_v1	v1_freq
1	yes	2
2	no	0
3	yes	1

nodupkey didn't work here....

Re: making a summarizing table from repeatedly measured data over time

novinosrin — Tue, 27 Nov 2018 21:09:29 GMT

Ok, kindly clarify this.

Should a by group(id) have both no and yes, do you want only the counts of yes and ignore no?

Re: making a summarizing table from repeatedly measured data over time

asinusdk — Tue, 27 Nov 2018 21:11:21 GMT

That's true. What I want to know is the proportion of a subject that has at least one YES in v1 over the time period. Thanks.

Re: making a summarizing table from repeatedly measured data over time

novinosrin — Tue, 27 Nov 2018 21:22:59 GMT

Thank you @asinusdk for clarifying


data have;
input time	id	v1 $;
cards;
1	1	no
2	1	no
3	1	yes
4	1	yes
1	2	no
2	2	no
3	2	no
4	2	no
1	3	yes
2	3	no
3	3	no
4	3	no
;
proc sort data=have out=_have;
by id v1;
run;


data want;
set _have;
by id v1 ;
if first.v1 then v1_freq=1;
else v1_freq+1;
if last.id ;
v1_freq=ifn(v1='yes',v1_freq,0);
keep id v1:;
run;

Re: making a summarizing table from repeatedly measured data over time

asinusdk — Tue, 27 Nov 2018 21:38:16 GMT

Thank you so much. You saved my life.

Re: making a summarizing table from repeatedly measured data over time

novinosrin — Tue, 27 Nov 2018 21:43:46 GMT

That was a very minor change. I hope you are able to understand the change and follow the code. The sorting of NO and YES made it easy as YES in collating sequence will always be 2nd to NO and so when you process last.id (last obs of the group), it would always be YES if there were YES and NO in a by group and if it's only NO, it;s of course NO and it;s count.

The IFN function simply resets the NO count to zero to meet your requirement. Anyways, feel free to write back to us if you need any help. Cheers!

Re: making a summarizing table from repeatedly measured data over time

asinusdk — Tue, 27 Nov 2018 21:49:14 GMT

Thanks for the explanation! I'm trying to understand the logic.

Re: making a summarizing table from repeatedly measured data over time

Reeza — Tue, 27 Nov 2018 22:36:07 GMT

@asinusdk wrote:
That's true. What I want to know is the proportion of a subject that has at least one YES in v1 over the time period. Thanks.

So you want to be able to say 2/3 patients had at least yes at one point over the time period? If that's the case, you're better off using an indicator flag rather than a count.

data have;
input time	id	v1 $;
cards;
1	1	no
2	1	no
3	1	yes
4	1	yes
1	2	no
2	2	no
3	2	no
4	2	no
1	3	yes
2	3	no
3	3	no
4	3	no
;
proc sort data=have out=_have;
by id descending v1;
run;


data want;
set _have;
by id descending v1 ;
flag=0;
if first.id and v1='yes' then flag=1;
if first.id then output;
run;

proc means data=want mean;
var flag;
run;

The output will be 66.67%, ie 2/3 have yes.

Re: making a summarizing table from repeatedly measured data over time

novinosrin — Tue, 27 Nov 2018 23:03:08 GMT

I wanted to have some fun with proc sql

data have;
input time	id	v1 $;
cards;
1	1	no
2	1	no
3	1	yes
4	1	yes
1	2	no
2	2	no
3	2	no
4	2	no
1	3	yes
2	3	no
3	3	no
4	3	no
;
proc sql;
create table want as
select distinct id ,v1, sum(v1='yes') as v1_freq
from have
group by id
having count(distinct v1)>=1 and v1='yes' or count(distinct v1)=1 and v1='no';
quit;