How to know additional value in a variable for a list of sequence

[Ravindra_]

I have a dataset with multiple timepoints and every visit has timepoint collected 3 times and if there is any timepoint that is collected more than 3 times it should come in output. I had written below code but it is not working. I had transposed all timepoints and got them into one variable called timepoint.

data abc_1;
	set abc;
	by SubjectId visit timepoint;
	Seq+1;
	if first.timepoint then Seq=1;
	if seq>3 then flag=1;
run;

example dataset as below

data ndsn;
infile datalines;
input subject visit $4. TP1 TP2 TP3;
datalines;
101 Day1 1  1  1
101 Day1 1  2  2
101 Day1 3  3  3
101 Day1 4  4  4
101 Day1 7  7  7
101 Day1 7  8  8
101 Day1 9  9  9
101 Day1 9 10 10
101 Day1 11 11 11
101 Day2 1  1  1
101 Day2 2  2  2
101 Day2 2  3  3
101 Day2 4  4  4
101 Day2 5  5  5
101 Day2 5  6  6
101 Day2 7  7  7
101 Day2 10 10 10
101 Day2 11 10 11
;
run;

desired output as below after transposing the 3 variables to one variable called timepoint i got that variable

data ndsn;
infile datalines;
input subject visit $4. timepoint;
datalines;
101 Day1 1 
101 Day1 7 
101 Day1 9
101 Day2 2 
101 Day2 5 
101 Day2 10
;
run;

Any help please

[Oligolas · Accepted solution]

Hi,

you could use SQL to achieve this:

PROC SQL;
   CREATE TABLE ndsn_t AS
      SELECT subject,visit,TP,count(TP) AS COUNT
      FROM
        (SELECT subject,visit,TP1 AS TP FROM ndsn
         UNION CORRESPONDING ALL 
         SELECT subject,visit,TP2 AS TP FROM ndsn
         UNION CORRESPONDING ALL 
         SELECT subject,visit,TP3 AS TP FROM ndsn)
      GROUP BY subject,visit,TP
      HAVING COUNT>3
      ORDER BY subject,visit,TP
   ;
QUIT;

[Ravindra_]

Thanks a lot for the solution, i will try this and will let you know if i need any help further

[PeterClemmensen]

Try this

data ndsn;
infile datalines;
input subject visit $4. TP1 TP2 TP3;
datalines;
101 Day1 1  1  1
101 Day1 1  2  2
101 Day1 3  3  3
101 Day1 4  4  4
101 Day1 7  7  7
101 Day1 7  8  8
101 Day1 9  9  9
101 Day1 9 10 10
101 Day1 11 11 11
101 Day2 1  1  1
101 Day2 2  2  2
101 Day2 2  3  3
101 Day2 4  4  4
101 Day2 5  5  5
101 Day2 5  6  6
101 Day2 7  7  7
101 Day2 10 10 10
101 Day2 11 10 11
;
run;

data temp(drop = tp:);
   set ndsn;
   array t tp:;
   do over t;
      timepoint = t;
      output;
   end;
run;

proc summary data = temp nway;
   class subject visit timepoint;
   var timepoint;
   output out = want(where = (_freq_ > 3)) n =;
run;

[Ravindra_]

Thanks for the response, i will try this as well and will let you know for any support

[Ravindra_]

Although all the suggestions were useful. @Oligolas  i had used your suggestion in my program and it worked for me. thanks a lot for your time everyone