turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Calculate mean by ID/ Visit with multiple attempts...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-11-2017 01:34 PM

Hi Everyone,

I fell like I losing sleep on how to get this output set up! I have a dataset that has possible values of 0/1. I have multiple possible encounters with the same ID over a single visit (e.g. multiple attempts on a questionnaire during a single visit). So I need to sum the values by ID and VIsit# (new variable sum_visits) and then get the means from those sum_visits BY visit#. I think this can be done with proc tabulate but I can't set it up right. I keep getting Mean=1 so it isn't summing by ID/VIsit first. Any help is greatly appreciated!!!

I have data that looks like this

ID | Score | Visit |

A | 1 | 1 |

A | 0 | 1 |

A | 1 | 1 |

A | 1 | 1 |

A | 1 | 2 |

A | 0 | 2 |

A | 1 | 2 |

A | 1 | 2 |

B | 0 | 1 |

B | 1 | 1 |

B | 1 | 1 |

B | 0 | 1 |

B | 1 | 2 |

B | 1 | 2 |

B | 1 | 2 |

B | 1 | 2 |

Would like results that give something like this (but doesn't have to be set up the way (just an example of my calculations).

ID | Score | Visit | Sum_Visits | ||

A | 1 | 1 | |||

A | 0 | 1 | |||

A | 1 | 1 | |||

A | 1 | 1 | 3 | ||

A | 1 | 2 | |||

A | 0 | 2 | |||

A | 1 | 2 | |||

A | 1 | 2 | 3 | ||

B | 0 | 1 | |||

B | 1 | 1 | |||

B | 1 | 1 | |||

B | 0 | 1 | 2 | ||

B | 1 | 2 | |||

B | 1 | 2 | |||

B | 1 | 2 | |||

B | 1 | 2 | 4 | ||

Sum Vist 1* ID | 5 | ||||

Sum Visit2 *ID | 7 | ||||

Mean Visit 1 | 5/2= 2.5 | ||||

Mean Visit 2 | 7/2=3.5 |

Accepted Solutions

Solution

11-11-2017
04:35 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jenim514

11-11-2017 02:55 PM

SQL is a *natural* for this:

```
proc sql;
create table want as
select visit, mean(totalScore) as meanTotalScore
from
(select id, visit, sum(score) as totalScore
from have
group by id, visit)
group by visit;
select * from want;
quit;
```

PG

All Replies

Solution

11-11-2017
04:35 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jenim514

11-11-2017 02:55 PM

SQL is a *natural* for this:

```
proc sql;
create table want as
select visit, mean(totalScore) as meanTotalScore
from
(select id, visit, sum(score) as totalScore
from have
group by id, visit)
group by visit;
select * from want;
quit;
```

PG

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

11-11-2017 04:25 PM

This is awesome!! I'm trying to add just one more new variable (not in the have dataset)...with a specific value of '004',=. Can you show me where this goes? Here is my attempt...didn't work.

proc sql;

create table sep_004 as

select visit, mean (totalscore) as mean, max(totalscore) as max, min(totalscore) as min,

std(totalscore) as std, nmiss(totalscore) as nmiss, n(totalscore) as n

from

(select PID, visit, sum(Q1) as totalscore

from sep004

group by PID, visit)

group by visit;

select * from sep_test;

Study='004';

quit;

proc sql;

create table sep_004 as

select visit, mean (totalscore) as mean, max(totalscore) as max, min(totalscore) as min,

std(totalscore) as std, nmiss(totalscore) as nmiss, n(totalscore) as n

from

(select PID, visit, sum(Q1) as totalscore

from sep004

group by PID, visit)

group by visit;

select * from sep_test;

Study='004';

quit;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jenim514

11-11-2017 04:34 PM

NVMD! Got it!

proc sql;

create table sep_005 as

select visit, mean (totalscore) as mean, max(totalscore) as max, min(totalscore) as min,

std(totalscore) as std, nmiss(totalscore) as nmiss, n(totalscore) as n, ('005') as Study

from

(select PID, visit, sum(Q1) as totalscore

from sep005

group by PID, visit)

group by visit;

select * from sep_005;

quit;

proc sql;

create table sep_005 as

select visit, mean (totalscore) as mean, max(totalscore) as max, min(totalscore) as min,

std(totalscore) as std, nmiss(totalscore) as nmiss, n(totalscore) as n, ('005') as Study

from

(select PID, visit, sum(Q1) as totalscore

from sep005

group by PID, visit)

group by visit;

select * from sep_005;

quit;