topic Re: Creating a binary variable from the presence of consecutive values in a set of other variables in SAS Programming

Creating a binary variable from the presence of consecutive values in a set of other variables

amrodri — Thu, 02 Oct 2014 16:31:41 GMT

Hello I am not sure if someone could help me with the following:

I have a longitudinal dataset of a binary variable that is equal to 1 when there is an infection in a patient, otherwise it is 0. Sometimes infections persist for seveal visits, sometimes they only remain for one visit and the patient is cured.I want to create 3 variables (X!, X2, X3) that would indicate a 1 or a 0 according to the following criteria:

In other words, this is how is might look and what I which to have my program do:

ID visit1 visit2 visit3 visit4 VARIABLE X1 VARIABLE X2 VARIABLE X3

1 0 0 0 0 0 0 0

2 0 1 0 0 0 1 0

3 1 1 0 0 1 1 0

4 0 1 1 1 1 1 1

5 0 1 0 1 0 1 0

X1) Variable is = 1 when there is an infection for At least two or more consecutive visits per patient.

X2) Variable is = 1 when there is an infection for At least one visits per patient (Any infection).

X3) Variable is = 1 when there is an infection for At least three or more consecutive visits per patient.

Anyone can help me? I do not know how to solve this.

Thank you

Message was edited by: Ana Maria rodriguez

Re: Creating a binary variable from the presence of consecutive values in a set of other variables

ballardw — Thu, 02 Oct 2014 16:58:18 GMT

I think an example of your input and desired output might be in order.

Do you want the flag on the first of a series of consecutive, on the last or on all records of the sequence? ( relates to goals 1 and 3)

For goal 2 is the flag to be set against all records for the patient or a single one? If a single record which one? Or do you just need to know which patients have any infection ever?

Re: Creating a binary variable from the presence of consecutive values in a set of other variables

amrodri — Thu, 02 Oct 2014 17:18:34 GMT

Thank you ballardw. I have changed my question so it is clearer. Thank you

Re: Creating a binary variable from the presence of consecutive values in a set of other variables

ballardw — Thu, 02 Oct 2014 17:53:06 GMT

I believe your original question said the data was in long form with one record per visit data and a flag for the infection.

Do you really want at final transposed dataset? How many dates are in the original data? Transposing into a single record per patient has the potential to generate 100s if not 1000s of variables.

Re: Creating a binary variable from the presence of consecutive values in a set of other variables

amrodri — Thu, 02 Oct 2014 18:25:05 GMT

It is long form, I have just transformed it here in wide form to better illustrate what I want to do.

The number of visits per patient is 15.

Re: Creating a binary variable from the presence of consecutive values in a set of other variables

Reeza — Thu, 02 Oct 2014 19:11:11 GMT

Based on your data you can concatenate all the strings. Finding 11 in the string means that X1=1 and so forth. Simply replace the 4 in the code below with the 15 or how many ever variables you have.

data have;

input ID visit1 visit2 visit3 visit4 ;

cards;

1 0 0 0 0

2 0 1 0 0

3 1 1 0 0

4 0 1 1 1

5 0 1 0 1

;

run;

data want;

set have;

array visit(4) visit1-visit4;

string=cats(of visit1-visit4);

x1=0; x2=0; x3=0;

if find(string, '11')>0 then x1=1;

if find(string, '1')>0 then x2=1;

if find(string, '111')>0 then x3=1;

run;

Re: Creating a binary variable from the presence of consecutive values in a set of other variables

Ksharp — Fri, 03 Oct 2014 12:17:26 GMT

Why not :

sum=sum(of visit:);

x1=ifn( sum=2,1,0 );

Re: Creating a binary variable from the presence of consecutive values in a set of other variables

data_null__ — Fri, 03 Oct 2014 12:55:39 GMT

Ksharp wrote:

Why not :
sum=sum(of visit:);
x1=ifn( sum=2,1,0 );

I think the '1's need to be contiguous visits. Right?

Re: Creating a binary variable from the presence of consecutive values in a set of other variables

Ksharp — Fri, 03 Oct 2014 13:31:46 GMT

Oh. You are right. I browsed too fast !

Re: Creating a binary variable from the presence of consecutive values in a set of other variables

amrodri — Fri, 03 Oct 2014 14:02:57 GMT

Ksharp Does that mean that this suggestion will not work? Thanks for your input

Re: Creating a binary variable from the presence of consecutive values in a set of other variables

Haikuo — Fri, 03 Oct 2014 14:13:58 GMT

I will stick to the solution offered by , as for a SAS solution, that is as slick as you can go.

Re: Creating a binary variable from the presence of consecutive values in a set of other variables

Ksharp — Fri, 03 Oct 2014 14:51:01 GMT

No. Actually it is very good .

Re: Creating a binary variable from the presence of consecutive values in a set of other variables

stat_sas — Fri, 03 Oct 2014 15:04:14 GMT

data want(drop=i);
set have;

array visit{*} v:;

variable1=0;
variable2=0;
variable3=0;

/* At least 2 consecutive visits */

do i=2 to dim(visit)-1;
if visit[i-1] ne 0 and visit ne 0 and
visit=visit[i-1] then variable1=1;
end;

/* At least 1 consecutive visits */

do i=1 to dim(visit);
if visit > 0 then variable2=1;
end;

/* At least 3 consecutive visits */

do i=2 to dim(visit)-1;
if visit[i-1] ne 0 and visit ne 0 and visit[i+1] ne 0 and
visit=visit[i-1] and visit=visit[i+1] then variable3=1;
end;

run;