COunt by group

[GS2]

Using SAS 9.4

I have a variable on_opioids (yes/no) and I need to know if a person was ever a 'Yes'. The problem is I need to know within a record_ID variable and also within a time_point variable (3 categorical timepoints)

record_id	timepoint	opioid	WANT
2	pre	yes	yes
2	first	no	no
2	second	yes	yes
2	pre	no	yes

I would like something like above where if the record_id was ever on an opioid within the timepoint it reports a "yes". Thank you

[PaigeMiller]

proc freq data=have;
    tables record_id*timepoint*opioid/noprint out=_counts_;
run;

In the output data set _COUNTS_, if you have OPIOID='Yes' and the count is 1 or more, then this is what you are looking for.

[GS2]

So I do not need a count of how many times a 'Yes' occurs, I need a variable with a 'Yes' indicator so that I can run a cross tabs of that and a grouping variable. Thank you

[PaigeMiller]

If the COUNT of 'Yes' is 1 or more, you get the "Yes" indicator you are asking for. It sounds like you want to merge the output data set back into the original data set so that you can create this "Yes" indicator.

[novinosrin · Accepted solution]

Hi @GS2  Do you mean this by any chance?

data have;
input record_id $	timepoint $	opioid $;*	WANT;
cards;
2	pre	yes	yes
2	first	no	no
2	second	yes	yes
2	pre	no	yes
;

data want;
 do _n_=1 by 1 until(last.record_id);
  set have;
  by record_id;
  if timepoint^='first' and opioid='yes' then t=1;
 end;
 do _n_=1 to _n_;
  set have;
  length want $3;
  want='no';
  if t then if timepoint^='first' then want='yes';
  output;
 end;
 drop t;
run;

[GS2]

When I ran this code the new variable came out as "no" for all observations

[novinosrin]

The logic I assumed is this

1. Identify is there is Opioid is 'Yes' for a record where timepoint is not first. If this is identified successfully flag it.

2. For the same group record_id ,should the flag t=1 , assign want=yes for the records timepoint not ='first' and No for this record.

I trust you are able to understand the above logic. If this approach is correct, you would have to verify CASE SENSITIVITY of your values.  What is also important is your sample is a representative of your real. 

[GS2]

I understand the logic and double checked the case sensitivity but still came up all no

[PaigeMiller]

PROC FREQ works, doesn't matter what the capitalization is. Then you merge the PROC FREQ results back into the original data set.

[novinosrin]

hmm that's strange as it works for the sample 😞

[GS2]

My fault I added a space when changing the capitalization of an answer option. Thank you

[Tom]

It is not clear from you example what you want.  Also the variables in your example do not match the names in your description.

It sounds like you want two flags created, but your sample data only has one WANT variable.

Try this and see if either of these is what you want.

data have;
  input record_id timepoint $ opioid $ WANT $;
cards;
2 pre yes yes
2 first no no
2 second yes yes
2 pre no yes
;

proc sql ;
create table want as 
select a.*
     , b.any_time
     , c.this_time
from have a 
inner join (select record_id,max(opioid) as any_time from have group by record_id) b
on a.record_id = b.record_id 
inner join (select record_id,timepoint,max(opioid) as this_time from have group by record_id,timepoint) c
on a.record_id = c.record_id and a.timepoint=c.timepoint
order by record_id,timepoint,opioid
;
quit;
proc print;
run;

       record_                                               this_
Obs       id      timepoint    opioid    WANT    any_time    time

 1        2        first        no       no        yes        no
 2        2        pre          no       yes       yes        yes
 3        2        pre          yes      yes       yes        yes
 4        2        second       yes      yes       yes        yes