Modifying data in SAS: copying part of the value of a cell, adding missing data and labeling it

Question

I have three different questions about modifying a dataset in SAS. My data contains: the day and the specific number belonging to the tag which was registred by an antenna on a specific day.

I have three separate questions:

1) The tag numbers are continuous and range from 1 to 560. Can I easily add numbers within this range which have not been registred on a specific day. So, if 160-280 is not registered for 23-May and 40-190 for 24-May to add these non-registered numbers only for that specific day? (The non registered numbers are much more scattered and for a dataset encompassing a few weeks to much to do by hand).

2) Furthermore, I want to make a new variable saying a tag has been registered (1) or not (0). Would it work to make this variable and set it to 1, then add the missing variables and (assuming the new variable is not set for the new number) set the missing values to 0.

3) the last question would be in regard to the format of the registered numbers which is along the line of 528 000000000400 and 000 000000000054. I am only interested in the last three digits of the number and want to remove the others. If I could add the missing numbers I could make a new variable after the data has been sorted by date and the original transponder code but otherwise what would you suggest?

I would love some suggestions and thank you in advance.

This question could be simplified. – Ruskin Jun 11 '14 at 13:32 — Ruskin, Jun 11 '14 at 13:32

score 2 · Accepted Answer · answered Jun 11 '14 at 14:27

I am inventing some data here, I hope I got your questions right.

data chickens;
    do tag=1 to 560;
        output;
    end;
run;

data registered;                   
input date mmddyy8. antenna tag;
format date date7.;                
datalines;                         
01012014  1 1                      
01012014  1 2                      
01012014  1 6                      
01012014  1 8                      
01022014  1 1                      
01022014  1 2                      
01022014  1 7                      
01022014  1 9                      
01012014  2 2                         
01012014  2 3                         
01012014  2 4                         
01012014  2 7                         
01022014  2 4                         
01022014  2 5                         
01022014  2 8                         
01022014  2 9                         
;                                  
run;         
proc sql;
    create table dates as
    select distinct date, antenna
    from registered;

    create table DatesChickens as
    select date, antenna, tag
    from dates, chickens
    order by date, antenna, tag;
quit;

proc sort data=registered;
    by date antenna tag;
run;

data registered;
    merge registered(in=INR) DatesChickens;
    by date antenna tag;
    Registered=INR;
run;

data registeredNumbers;
input Numbers $16.;
datalines;
528 000000000400 
000 000000000054
;
run;

data registeredNumbers;
    set registeredNumbers;
    NewNumbers=substr(Numbers,14);
run;

Thank you very much, i have adapted your code slightly by removing the antenna factor but it is doing exactly what I intended to do with my data :D — user3729859, Jun 17 '14 at 11:11

score 0 · Answer 2 · answered Jun 11 '14 at 13:41

0

I do not know SAS, but here is how I would do it in SQL - may give you an idea of how to start.

1 - Birds that have not registered through pophole that day

SELECT b.BirdId
FROM Birds b
WHERE NOT EXISTS 
    (SELECT 1 FROM Pophole_Visits p WHERE b.BirdId = p.BirdId AND p.date = ????)

2 - Birds registered through pophole

If you have a dataset with pophole data you can query that to find if a bird has been through. What would you flag be doing - finding a bird that has never been through any popholes? Looking for dodgy sensor tags or dead birds?

3 - Data code

You might have more joy with the SUBSTRING function

Good luck

answered Jun 11 '14 at 13:41

Ruskin

5,721
4
45
62

Thank you for your contribution. To answer your questions the attempt with also identifying bird which have not registred is indeed to find faulty sensors. – user3729859 Jun 17 '14 at 11:09
You are better off leaving the data in the database to match the ids that come off the tags - then tweak on the front-end to only display the bits you need using SUBSTRING or similar – Ruskin Jun 18 '14 at 09:12

Modifying data in SAS: copying part of the value of a cell, adding missing data and labeling it

2 Answers2

1 - Birds that have not registered through pophole that day

2 - Birds registered through pophole

3 - Data code