BookmarkSubscribeRSS Feed
shasank
Quartz | Level 8

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.

1 REPLY 1
ballardw
Super User

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 637 views
  • 0 likes
  • 2 in conversation