SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to identify the ID which had no occurrence during initial enrollment but had new occurrence

Reply
Contributor
Posts: 20

How to identify the ID which had no occurrence during initial enrollment but had new occurrence

Hi SAS community,

 

 I have 4 datasets that have data on population across 6 time points.

  

Dataset 1

 

ID       Var 1

1           0

2           1

3           0

4           1

5           0

 

Dataset 2

 

ID       Var 1

1           0

2           1

3           0

4           0

5           1

6            1

 

Dataset 3

 

ID       Var 1

1           1

2           0

3           0

4           0

5           0

6           1

7           1

 

Dataset 4

 

ID       Var 1

1           1

2           0

3           0

4           1

5           0

6           1

7            0

8            0

9            0

 

Final Output dataset

2            1

4            1

5            1

6            1

7            1

8            1

9            1

The logic is that a patient was given questionnaires 1 per year for 4 years. I want to know who said no(0) his/her 1st questionnaire but later said yes(1) in his/her later questionnaire. new people could join in any wave and start from there.

 

I appreciate your help.

Super User
Posts: 11,517

Re: How to identify the ID which had no occurrence during initial enrollment but had new occurrence

Each of your contributing data sets should have an identifier, year or wave sounds likely. Combine the data sets with a set statement, sort by id, year (or wave). Assuming you have done such then this example is one way to get that information:

data example;
   input id var1 wave;
datalines;
1 0   1
1 0   2
1 0   3
1 0   4
2  0  1
2  1  2
2  1  3
2  1  4
3  0  3
3  0  4
4  0  3
4  1  4
;

data want;
   set example;
   by id wave;
   Retain FirstNo Changed;
   if first.id then do;
      FirstNo= (var1=0);
      changed=0;
   end;
   if changed=0 and var1=1 and FirstNo=1 then Changed=1;
   if last.id then output;
   
run;

The example data set is just to have something similar to a set with the id and wave values and sorted as needed.

 

The output set has the last wave value only and the variable changed is 1 if there was a change from 0 for the first var1 to 1. You could drop the FirstNo variable but you may want it to identify the ID's that had a first no.

 

It is a good idea to show what you expect for output. There are several ways this could be done.

 

If your actual data does not already have wave or year or similar identifier (Big mistake) then this is one way to add it combining the data sets:

data example;
   set 
      data1 (in=in1)
      data2 (in=in2)
      data3 (in=in3)
      data4 (in=in4);
  wave = in1 + 2*in2 + 3*in3 + 4*in4;
run;
proc sort data=example;
   by id wave;
run;

The IN= option creates a temporary variable with the name specified that has a value of 1 when the current record comes from that set and 0 otherwise. The assignment to wave will have a value of 1, 2, 3 or 4 depending on which set it is from since the IN variables will have a value of 1 for only one data set at a time.

 

Ask a Question
Discussion stats
  • 1 reply
  • 131 views
  • 0 likes
  • 2 in conversation