+ Reply to Thread
Results 1 to 11 of 11

Thread: SAS Coding to find averages for every 60 observations

  1. #1
    Points: 969, Level: 16
    Level completed: 69%, Points required for next Level: 31

    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts

    SAS Coding to find averages for every 60 observations




    Hi.

    I am working on an analysis for research on premature baby health. I ran into a SAS coding issue which I hope some of you can help me with.

    This is a repeated measures design. My outcome variable is SpO2. There are 20 subjects. Each subject has over 30,000 repeated measures (2 second intervals). The variables I have are ID, Time in seconds, and SpO2.

    I have 1 million observations total. I would like to break this up into parts.
    Instead of having observations for 2 second intervals, I would like to create hourly intervals. So I want to create one dataset that takes has the mean SpO2 for each hour for each subject. The dataset originally looks like this:

    ID Seconds SpO2
    1 0 100
    1 2 99
    1 4 100
    ...

    But I want it to look like this:

    ID Hours Mean_SpO2
    1 0 98
    1 1 100
    1 2 99
    ...




    I appreciate any help.
    Thank you.
    Last edited by Nives_ES; 07-28-2016 at 12:29 PM.

  2. #2
    Omega Contributor
    Points: 38,413, Level: 100
    Level completed: 0%, Points required for next Level: 0
    hlsmith's Avatar
    Location
    Not Ames, IA
    Posts
    7,004
    Thanks
    398
    Thanked 1,186 Times in 1,147 Posts

    Re: SAS Coding to find averages for every 60 observations

    This isn't correct, right:


    ID Seconds Mean_SpO2
    1 0 100
    1 2 99
    1 4 100


    You have ID Seconds and SpO2, correct. Not averages in your current data.
    Stop cowardice, ban guns!

  3. The Following User Says Thank You to hlsmith For This Useful Post:

    Nives_ES (07-29-2016)

  4. #3
    Points: 969, Level: 16
    Level completed: 69%, Points required for next Level: 31

    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: SAS Coding to find averages for every 60 observations

    Yes that's correct! Thanks for the edit! I made the changes!
    Last edited by Nives_ES; 07-28-2016 at 12:34 PM.

  5. #4
    Omega Contributor
    Points: 38,413, Level: 100
    Level completed: 0%, Points required for next Level: 0
    hlsmith's Avatar
    Location
    Not Ames, IA
    Posts
    7,004
    Thanks
    398
    Thanked 1,186 Times in 1,147 Posts

    Re: SAS Coding to find averages for every 60 observations

    Two steps, creating a categorical variable 30,000/30 = 1,000 minute groups and getting the averages for those groups and getting rid of duplicates so final dataset has about 1,000 averages.


    The second step can be done using proc means and a class option, then an output statement in that step. I don't have that code memorized but you tell SAS to calculate averages for strata and insert that back into the outputted dataset. Then you may need to run a proc sort with nodupkey option so then instead of 30,000 rows you have 1,000.


    I will think about the first step as well.
    Stop cowardice, ban guns!

  6. The Following User Says Thank You to hlsmith For This Useful Post:

    Nives_ES (07-29-2016)

  7. #5
    Points: 969, Level: 16
    Level completed: 69%, Points required for next Level: 31

    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: SAS Coding to find averages for every 60 observations

    That is a great idea. I can do the second part with proc means and using the output out= option. But as for the first part I am still stumped. How would I make a variable that can categorize the first n observations as "1" , the second n observations as "2" etc...
    I appreciate your help!

  8. #6
    Omega Contributor
    Points: 38,413, Level: 100
    Level completed: 0%, Points required for next Level: 0
    hlsmith's Avatar
    Location
    Not Ames, IA
    Posts
    7,004
    Thanks
    398
    Thanked 1,186 Times in 1,147 Posts

    Re: SAS Coding to find averages for every 60 observations

    Does everybody have the exact same number of measurements?
    Stop cowardice, ban guns!

  9. The Following User Says Thank You to hlsmith For This Useful Post:

    Nives_ES (07-29-2016)

  10. #7
    Points: 969, Level: 16
    Level completed: 69%, Points required for next Level: 31

    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: SAS Coding to find averages for every 60 observations

    No, there are some with 20,000 measurements and others with over 50,000. However, each subject has their own dataset meaning I will have to merge all their datasets for the analysis later on. To be clear, I have 20 different csv files and each file holds information on one unique subject in long format.

    So I believe that whatever process we do, we can run it on the individual files and then merge the data.
    Or we can merge first then clean and change but I believe that may be more complex.

  11. #8
    Omega Contributor
    Points: 38,413, Level: 100
    Level completed: 0%, Points required for next Level: 0
    hlsmith's Avatar
    Location
    Not Ames, IA
    Posts
    7,004
    Thanks
    398
    Thanked 1,186 Times in 1,147 Posts

    Re: SAS Coding to find averages for every 60 observations

    Alright, I suck at datasteps, but I think I figured out your first step. Create a new variable by dividing seconds by 30 and rounding all decimals up, not sure what to do if you have a zero value for seconds, I guess add 1 to all Second values and tweak the formula. You will have to clean up my code below and double check that it works:


    Code: 
    SpO2_min = round ((second / 30)), 1.0;
    P.S., Feel free to post your final code for all three steps, I would be interested in seeing it all put together.


    Let me know if you have questions on how to put the 3 steps together.


    1. Create categorical variable
    2. Get average
    3. Get rid of duplicate values for a group
    Stop cowardice, ban guns!

  12. The Following User Says Thank You to hlsmith For This Useful Post:

    Nives_ES (07-29-2016)

  13. #9
    Points: 969, Level: 16
    Level completed: 69%, Points required for next Level: 31

    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: SAS Coding to find averages for every 60 observations

    Thank you very much! I will be posting some of the coding I used to solve this issue!

  14. #10
    Points: 969, Level: 16
    Level completed: 69%, Points required for next Level: 31

    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: SAS Coding to find averages for every 60 observations

    %Macro Import(Name,ID);
    Proc Import file="&Name" dbms=EXCEL out=&ID replace;
    getnames=yes;
    run;

    Data &ID;
    set &ID;
    Hours = CEIL ((Seconds/3600));
    run;


    Proc Means data=&ID NOPRINT;
    class Hours;
    var SpO2;
    output out=M&ID mean=mean_SpO2;
    run;

    Data MS6;
    set MS6;
    ID="&ID";
    Drop _FREQ_ _TYPE_;
    run;

    %Mend;

    I ran this macro for every subject's file.
    All that's left is for me to merge datasets.
    Thanks a bunch!!!

  15. #11
    Omega Contributor
    Points: 38,413, Level: 100
    Level completed: 0%, Points required for next Level: 0
    hlsmith's Avatar
    Location
    Not Ames, IA
    Posts
    7,004
    Thanks
    398
    Thanked 1,186 Times in 1,147 Posts

    Re: SAS Coding to find averages for every 60 observations


    No problem. It is not that often that I feel so useful.
    Stop cowardice, ban guns!

+ Reply to Thread

           




Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts






Advertise on Talk Stats