DATA Step, Macro, Functions and more

Conditionally collapse data across multiple observations

Reply
New Contributor
Posts: 2

Conditionally collapse data across multiple observations

Greetings;

 

I have data from a food intake monitoring system that records feeding behavior of animals. This data includes every time an animal entered the system, removed food and left the system. Thus, the system records very brief interruptions in food intake as different observation, but these eating bouts are actually part of the same meal. Therefore, I am attempting to conditionally collapse the data recorded as separate observations but that are actually part of the same meal (i.e., all observations that do not have at least 30 minutes between each eating event [a.k.a, IMI or intermeal interval]).

 

I have placed the code that I have generated below. So far, when I run this code in SAS, the program gets hung-up in processing the data step; I have allowed SAS up to 8 hours to try and compute with no success and have to manually terminate.

 

Thanks in advance for any assistance. The code that I have been working with so far is:

 

data conditional;
    set ac.example_uncollapsed;  /*This code simply refers to an earlier reference of the dataset that corresponds to the excel                                                                /*attachment in this post*/
    sumintake = intake;

     IF IMI gt 1800 then

            DO;

                sumintake = 0;
            DO WHILE(IMI le 1800);
                BY StartTime notsorted EndTime notsorted IMI notsorted;

                StartTime = first.StartTime;

                EndTime = last.EndTime;

                sumintake + intake;

                IF last.EndTime then

                     DO;

                       output;

                     END;

            END;

       END;
                meal_number +1;
                run;

Super User
Posts: 23,295

Re: Conditionally collapse data across multiple observations

[ Edited ]

1. Can feed times cross dates?

2. IMI is already calculated it looks like, so there's no need to do that, is that correct? 

3. How many feedings can be grouped and is it 30 mins from start or just that if IMI is less than 30 it belongs to the same group?

 

SAS stores times as seconds, so you need to convert 30 mins to seconds, but incrementing group for each if the IMI is over seems simple enough. Then you can use PROC MEANS or SUMMARY to collapse the data. You could also add it together within the data step, but I prefer summary procedures for summary. 

 

EDIT: And unless you have 100 millions of rows, 8 hours is too long to process a data set. It takes me 20 minutes to process approximately 30 million records - with 8GB of RAM and reading across networks. 

 

data want;
set have;
by id;
retain group;

if first.id then group=0;

if imi > (30*60) then group + 1; 

run;

 


DWBrake wrote:

Greetings;

 

I have data from a food intake monitoring system that records feeding behavior of animals. This data includes every time an animal entered the system, removed food and left the system. Thus, the system records very brief interruptions in food intake as different observation, but these eating bouts are actually part of the same meal. Therefore, I am attempting to conditionally collapse the data recorded as separate observations but that are actually part of the same meal (i.e., all observations that do not have at least 30 minutes between each eating event [a.k.a, IMI or intermeal interval]).

 

I have placed the code that I have generated below. So far, when I run this code in SAS, the program gets hung-up in processing the data step; I have allowed SAS up to 8 hours to try and compute with no success and have to manually terminate.

 

Thanks in advance for any assistance. The code that I have been working with so far is:

 

data conditional;
    set ac.example_uncollapsed;  /*This code simply refers to an earlier reference of the dataset that corresponds to the excel                                                                /*attachment in this post*/
    sumintake = intake;

     IF IMI gt 1800 then

            DO;

                sumintake = 0;
            DO WHILE(IMI le 1800);
                BY StartTime notsorted EndTime notsorted IMI notsorted;

                StartTime = first.StartTime;

                EndTime = last.EndTime;

                sumintake + intake;

                IF last.EndTime then

                     DO;

                       output;

                     END;

            END;

       END;
                meal_number +1;
                run;



 

New Contributor
Posts: 2

Re: Conditionally collapse data across multiple observations

Thank you for your suggestion Reeza; your suggested solution should work well and is a simpler approach than what I was attempting.

 

To answer your questions:


1. Can feed times cross dates?

           DWB: Yes; however, I only intend to generate daily summaries because the analyses will be conducted using repeated measures and day will be the repeated term.

 

2. IMI is already calculated it looks like, so there's no need to do that, is that correct?

           DWB: That is correct. I have already calculated IMI.

 

3. How many feedings can be grouped and is it 30 mins from start or just that if IMI is less than 30 it belongs to the same group?

          DWB: The amount of feedings that can be grouped is variable and dependent on the individual behavior of each animal. The grouping is dependent on if IMI is less than 30 min; not based on the start time.

 

Thanks again for the help.

DB

Super User
Posts: 13,321

Re: Conditionally collapse data across multiple observations

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

 

Sort of goes double for the XLS.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Ask a Question
Discussion stats
  • 3 replies
  • 120 views
  • 0 likes
  • 3 in conversation