Assign value to new variable based on criteria from existing dataset 2

[Forester83]

Hi, I am new to SAS and just learning some data manipulation techniques. I have a dataset from a mark-and-recapture study of small mammals. Animals were caught over three days over three separate sessions (9 days in total). Individuals were given an ear tag and then released.

There are two procedures that I want to accomplish.

First, I would like to assign a “Y” in the New_Ind column the first time an individual is captured. All subsequent records should have an “N” in the New_Ind column. I believe that in order to do this the dataset must first be sorted by Site, Species, Ear_Tag, and Date then assign a value of “Y” to first occurrence of each Ear_Tag number. However, I do not know how to do the latter part of this procedure.

Second, I would like to assign an “N” in the Recapture column the first time an individual is caught within each of the three sessions. All subsequent records for that individual within that session should have a “Y” in the Recapture column. I think that in order to do this the dataset must first be sorted by Session, Site, Species, Ear_Tag and Date then assign a value of “N” to the first occurrence of each Ear_Tag number within each session. Once again I am not sure how to accomplish the latter part of this procedure.

Notes on the data:
Data was collected over three sessions and for three dates within each session (9 days in total [S1 = 20-May-08, 21-May-08, 22-May-08; S2 = 20-Jun-08, 21-Jun-08, 22-Jun-08; S3 = 20-Jul-09, 21-Jul-08, 22-Jul-08]). There are three sites (Grid1, Grid2, Grid3) and two species (DEMO, RESQ). Each small mammal individual has an ear tag number identifying it.

Any help would be greatly appreciated!

Cheers,

Mark


Here is the data as cards with the new columns included:

Date Session Site Species Ear_Tag Recapture New_Ind
20-May-08 S1 Grid1 DEMO 1002
20-May-08 S1 Grid1 RESQ 1000
20-May-08 S1 Grid1 RESQ 1004
20-May-08 S1 Grid1 RESQ 1003
20-May-08 S1 Grid1 DEMO 1001
20-May-08 S1 Grid2 DEMO 1006
20-May-08 S1 Grid2 RESQ 1008
20-May-08 S1 Grid2 DEMO 1009
20-May-08 S1 Grid2 DEMO 1007
20-May-08 S1 Grid2 RESQ 1010
20-May-08 S1 Grid2 DEMO 1005
20-May-08 S1 Grid3 RESQ 1012
20-May-08 S1 Grid3 DEMO 1013
20-May-08 S1 Grid3 DEMO 1016
20-May-08 S1 Grid3 DEMO 1014
20-May-08 S1 Grid3 DEMO 1015
20-May-08 S1 Grid3 RESQ 1011
21-May-08 S1 Grid1 DEMO 1017
21-May-08 S1 Grid1 RESQ 1000
21-May-08 S1 Grid1 RESQ 1003
21-May-08 S1 Grid1 RESQ 1018
21-May-08 S1 Grid1 DEMO 1001
21-May-08 S1 Grid1 RESQ 1019
21-May-08 S1 Grid1 RESQ 1004
21-May-08 S1 Grid2 RESQ 1024
21-May-08 S1 Grid2 RESQ 1008
21-May-08 S1 Grid2 DEMO 1007
21-May-08 S1 Grid2 DEMO 1020
21-May-08 S1 Grid2 RESQ 1022
21-May-08 S1 Grid2 RESQ 1023
21-May-08 S1 Grid2 RESQ 1010
21-May-08 S1 Grid2 DEMO 1009
21-May-08 S1 Grid3 DEMO 1013
21-May-08 S1 Grid3 RESQ 1011
21-May-08 S1 Grid3 RESQ 1012
21-May-08 S1 Grid3 DEMO 1041
22-May-08 S1 Grid1 RESQ 1004
22-May-08 S1 Grid1 RESQ 1019
22-May-08 S1 Grid1 RESQ 1018
22-May-08 S1 Grid1 RESQ 1000
22-May-08 S1 Grid1 DEMO 1021
22-May-08 S1 Grid1 DEMO 1001
22-May-08 S1 Grid1 RESQ 1003
22-May-08 S1 Grid2 DEMO 1009
22-May-08 S1 Grid2 RESQ 1023
22-May-08 S1 Grid2 RESQ 1010
22-May-08 S1 Grid2 DEMO 1005
22-May-08 S1 Grid2 DEMO 1020
22-May-08 S1 Grid2 RESQ 1022
22-May-08 S1 Grid2 RESQ 1024
22-May-08 S1 Grid2 DEMO 1006
22-May-08 S1 Grid3 RESQ 1011
22-May-08 S1 Grid3 DEMO 1015
22-May-08 S1 Grid3 RESQ 1012
22-May-08 S1 Grid3 DEMO 1013
22-May-08 S1 Grid3 DEMO 1041
22-May-08 S1 Grid3 DEMO 1014
22-May-08 S1 Grid3 DEMO 1016
20-Jun-08 S2 Grid1 RESQ 1003
20-Jun-08 S2 Grid1 RESQ 1004
20-Jun-08 S2 Grid1 RESQ 1026
20-Jun-08 S2 Grid1 RESQ 1025
20-Jun-08 S2 Grid1 DEMO 1021
20-Jun-08 S2 Grid1 RESQ 1000
20-Jun-08 S2 Grid1 DEMO 1017
20-Jun-08 S2 Grid1 DEMO 1001
20-Jun-08 S2 Grid2 DEMO 1007
20-Jun-08 S2 Grid2 DEMO 1005
20-Jun-08 S2 Grid2 DEMO 1023
20-Jun-08 S2 Grid2 RESQ 1030
20-Jun-08 S2 Grid2 RESQ 1008
20-Jun-08 S2 Grid2 DEMO 1029
20-Jun-08 S2 Grid2 DEMO 1020
20-Jun-08 S2 Grid3 RESQ 1034
20-Jun-08 S2 Grid3 RESQ 1011
20-Jun-08 S2 Grid3 DEMO 1014
20-Jun-08 S2 Grid3 DEMO 1013
20-Jun-08 S2 Grid3 RESQ 1035
20-Jun-08 S2 Grid3 DEMO 1041
21-Jun-08 S2 Grid1 RESQ 1000
21-Jun-08 S2 Grid1 DEMO 1017
21-Jun-08 S2 Grid1 RESQ 1004
21-Jun-08 S2 Grid1 RESQ 1018
21-Jun-08 S2 Grid1 DEMO 1002
21-Jun-08 S2 Grid1 RESQ 1003
21-Jun-08 S2 Grid1 RESQ 1026
21-Jun-08 S2 Grid2 DEMO 1007
21-Jun-08 S2 Grid2 DEMO 1032
21-Jun-08 S2 Grid2 RESQ 1008
21-Jun-08 S2 Grid2 DEMO 1031
21-Jun-08 S2 Grid2 DEMO 1009
21-Jun-08 S2 Grid2 RESQ 1022
21-Jun-08 S2 Grid2 RESQ 1033
21-Jun-08 S2 Grid2 RESQ 1024
21-Jun-08 S2 Grid2 DEMO 1030
21-Jun-08 S2 Grid2 DEMO 1023
21-Jun-08 S2 Grid2 DEMO 1029
21-Jun-08 S2 Grid3 DEMO 1015
21-Jun-08 S2 Grid3 RESQ 1011
21-Jun-08 S2 Grid3 RESQ 1035
21-Jun-08 S2 Grid3 DEMO 1013
21-Jun-08 S2 Grid3 DEMO 1016
21-Jun-08 S2 Grid3 RESQ 1012
22-Jun-08 S2 Grid1 RESQ 1019
22-Jun-08 S2 Grid1 RESQ 1003
22-Jun-08 S2 Grid1 DEMO 1017
22-Jun-08 S2 Grid1 RESQ 1028
22-Jun-08 S2 Grid1 RESQ 1004
22-Jun-08 S2 Grid1 DEMO 1027
22-Jun-08 S2 Grid1 RESQ 1018
22-Jun-08 S2 Grid1 DEMO 1021
22-Jun-08 S2 Grid1 DEMO 1001
22-Jun-08 S2 Grid2 DEMO 1020
22-Jun-08 S2 Grid2 RESQ 1030
22-Jun-08 S2 Grid2 RESQ 1010
22-Jun-08 S2 Grid2 DEMO 1006
22-Jun-08 S2 Grid2 DEMO 1005
22-Jun-08 S2 Grid2 DEMO 1023
22-Jun-08 S2 Grid3 DEMO 1036
22-Jun-08 S2 Grid3 DEMO 1037
22-Jun-08 S2 Grid3 RESQ 1034
22-Jun-08 S2 Grid3 DEMO 1014
22-Jun-08 S2 Grid3 DEMO 1041
22-Jun-08 S2 Grid3 RESQ 1040
22-Jun-08 S2 Grid3 RESQ 1038
22-Jun-08 S2 Grid3 RESQ 1011
22-Jun-08 S2 Grid3 RESQ 1012
22-Jun-08 S2 Grid3 RESQ 1039
22-Jun-08 S2 Grid3 DEMO 1013
20-Jul-08 S3 Grid1 DEMO 1027
20-Jul-08 S3 Grid1 DEMO 1017
20-Jul-08 S3 Grid1 DEMO 1043
20-Jul-08 S3 Grid1 RESQ 1000
20-Jul-08 S3 Grid1 DEMO 1021
20-Jul-08 S3 Grid1 RESQ 1004
20-Jul-08 S3 Grid1 RESQ 1018
20-Jul-08 S3 Grid1 DEMO 1001
20-Jul-08 S3 Grid1 DEMO 1042
20-Jul-08 S3 Grid1 RESQ 1025
20-Jul-08 S3 Grid2 RESQ 1010
20-Jul-08 S3 Grid2 DEMO 1047
20-Jul-08 S3 Grid2 DEMO 1020
20-Jul-08 S3 Grid2 DEMO 1029
20-Jul-08 S3 Grid2 DEMO 1032
20-Jul-08 S3 Grid2 DEMO 1009
20-Jul-08 S3 Grid2 RESQ 1030
20-Jul-08 S3 Grid2 DEMO 1007
20-Jul-08 S3 Grid2 RESQ 1046
20-Jul-08 S3 Grid2 DEMO 1031
20-Jul-08 S3 Grid2 DEMO 1006
20-Jul-08 S3 Grid3 RESQ 1039
20-Jul-08 S3 Grid3 RESQ 1049
20-Jul-08 S3 Grid3 RESQ 1012
20-Jul-08 S3 Grid3 DEMO 1015
20-Jul-08 S3 Grid3 RESQ 1050
20-Jul-08 S3 Grid3 DEMO 1016
20-Jul-08 S3 Grid3 DEMO 1013
20-Jul-08 S3 Grid3 DEMO 1051
20-Jul-08 S3 Grid3 RESQ 1038
20-Jul-08 S3 Grid3 RESQ 1034
20-Jul-08 S3 Grid3 RESQ 1035
21-Jul-08 S3 Grid1 DEMO 1002
21-Jul-08 S3 Grid1 DEMO 1001
21-Jul-08 S3 Grid1 DEMO 1042
21-Jul-08 S3 Grid1 RESQ 1026
21-Jul-08 S3 Grid1 RESQ 1025
21-Jul-08 S3 Grid1 RESQ 1028
21-Jul-08 S3 Grid1 RESQ 1018
21-Jul-08 S3 Grid1 DEMO 1027
21-Jul-08 S3 Grid1 DEMO 1017
21-Jul-08 S3 Grid1 RESQ 1044
21-Jul-08 S3 Grid1 RESQ 1019
21-Jul-08 S3 Grid1 DEMO 1043
21-Jul-08 S3 Grid2 RESQ 1033
21-Jul-08 S3 Grid2 DEMO 1006
21-Jul-08 S3 Grid2 DEMO 1032
21-Jul-08 S3 Grid2 DEMO 1009
21-Jul-08 S3 Grid2 DEMO 1020
21-Jul-08 S3 Grid2 RESQ 1010
21-Jul-08 S3 Grid2 RESQ 1024
21-Jul-08 S3 Grid2 DEMO 1047
21-Jul-08 S3 Grid2 DEMO 1029
21-Jul-08 S3 Grid2 DEMO 1048
21-Jul-08 S3 Grid2 DEMO 1005
21-Jul-08 S3 Grid3 RESQ 1011
21-Jul-08 S3 Grid3 RESQ 1035
21-Jul-08 S3 Grid3 DEMO 1015
21-Jul-08 S3 Grid3 RESQ 1034
21-Jul-08 S3 Grid3 DEMO 1051
21-Jul-08 S3 Grid3 RESQ 1040
21-Jul-08 S3 Grid3 RESQ 1038
21-Jul-08 S3 Grid3 DEMO 1041
21-Jul-08 S3 Grid3 DEMO 1013
21-Jul-08 S3 Grid3 RESQ 1039
21-Jul-08 S3 Grid3 RESQ 1052
22-Jul-08 S3 Grid1 DEMO 1027
22-Jul-08 S3 Grid1 RESQ 1025
22-Jul-08 S3 Grid1 RESQ 1044
22-Jul-08 S3 Grid1 RESQ 1004
22-Jul-08 S3 Grid1 DEMO 1002
22-Jul-08 S3 Grid1 DEMO 1045
22-Jul-08 S3 Grid1 DEMO 1042
22-Jul-08 S3 Grid1 RESQ 1003
22-Jul-08 S3 Grid1 RESQ 1019
22-Jul-08 S3 Grid1 DEMO 1021
22-Jul-08 S3 Grid2 RESQ 1030
22-Jul-08 S3 Grid2 RESQ 1023
22-Jul-08 S3 Grid2 RESQ 1046
22-Jul-08 S3 Grid2 RESQ 1024
22-Jul-08 S3 Grid2 DEMO 1009
22-Jul-08 S3 Grid2 DEMO 1020
22-Jul-08 S3 Grid2 RESQ 1010
22-Jul-08 S3 Grid2 RESQ 1022
22-Jul-08 S3 Grid2 DEMO 1048
22-Jul-08 S3 Grid2 RESQ 1033
22-Jul-08 S3 Grid2 DEMO 1005
22-Jul-08 S3 Grid2 DEMO 1031
22-Jul-08 S3 Grid3 DEMO 1054
22-Jul-08 S3 Grid3 RESQ 1012
22-Jul-08 S3 Grid3 DEMO 1016
22-Jul-08 S3 Grid3 DEMO 1036
22-Jul-08 S3 Grid3 DEMO 1037
22-Jul-08 S3 Grid3 RESQ 1040
22-Jul-08 S3 Grid3 DEMO 1014
22-Jul-08 S3 Grid3 RESQ 1053
22-Jul-08 S3 Grid3 DEMO 1055
22-Jul-08 S3 Grid3 RESQ 1011

 

[ChrisW75]

OK, I'll answer the first one for you, the second one you should be able to assemble from the first. It's basically the same problem.

proc sort data=capture_data;
  by ear_Tag date;
run;

data capture_data2;
  set capture_data;
  by ear_tag;

  if first.ear_tag then new_ind = 'Y';
  else new_ind = 'N';
run;

This is known as 'By Group Processing'. By setting a BY statement in the datastep, you have at your disposal a temporary variable (which is not written out) called last.<by variable> and first.<by variable>. The values of this are TRUE or FALSE (1 or 0 if used arithmetically).

Your second example is SLIGHTLY more complicated in that you're going to need multiple variables in your by group.
The only thing you need to know is that if the more important variable in the by group order changes, then all subsequent ones are considered to be new as well.
ie, if your by group is VAR1 VAR2 VAR3, and VAR1 is the same as the previous value of VAR1 and VAR2 is different to the previous value of VAR2, then FIRST.VAR3 will be true irrespective of whether it is the same or different to the value before. This makes sense when you see it mapped out in the data.
Here is the full doco for this topic:-

http://support.sas.com/onlinedoc/913/getDoc/en/lrcon.hlp/a001283274.htm

It's worth checking out as it's a very powerful tool.

If you get stuck, post back and I'll lay it out for you.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[Forester83]

Hi Chris,

Thanks for the help. I was able to conquer the first task with you notes; however, I am having difficulty with second task. Could I ask for you help again?

Cheers,

Mark

 

[klaz2002]

I found that you can this by doing the following.

proc sort
  data = test;
  by ear_tag session date;
run; 

data test2;
  set test;
  by ear_tag session date;
  if first.session then
    recapture = 'N';
  else
    recapture = 'Y';
run;

I hope that this helps you.
Klaz