topic Re: How to know additional value in a variable for a list of sequence in SAS Programming

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

Ravindra_ — Wed, 21 Apr 2021 15:03:33 GMT

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

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

Oligolas — Wed, 21 Apr 2021 15:44:58 GMT

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;

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

PeterClemmensen — Wed, 21 Apr 2021 18:17:37 GMT

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;

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

Ravindra_ — Wed, 21 Apr 2021 18:25:40 GMT

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

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

Ravindra_ — Wed, 21 Apr 2021 18:26:54 GMT

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

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

Ravindra_ — Thu, 22 Apr 2021 09:54:17 GMT

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