Newbie with proc sql help with basic statements (mean, max, min, etc)

[user1942]

Hello experts, I'm starting to learn proc sql and I'm having trouble completing this exercise. I need to find:

a) How many patients by gender?

b) How many doctor visits for each patient with medical test score above 50?

c) what are mean, min, max scores for each patient?

d) list patients who have mean scores for all their visits larger than 45.

I've tried to do part B so here is my code for part of this exercise, however I'm not getting the numbers I want:

data record;
input ID $ Gender $ Age Score;
cards;
259632 F 56 58
259632 F 56 41
259632 F 56 39
577763 F 67 40
577763 F 67 50
577763 F 67 39
577763 F 67 33
279645 M 52 24
279645 M 52 65
279645 M 52 66
279645 M 52 74
279645 M 52 85
694797 F 48 37
694797 F 48 85
684516 M 57 81
760076 M 62 45
760076 M 62 35
760076 M 62 38
760076 M 62 65
745795 F 74 85
745795 F 74 82
745795 F 74 77
745795 F 74 81
506301 M 78 70
506301 M 78 70
506301 M 78 71
506301 M 78 67
406126 M 62 60
406126 M 62 50
477908 M 70 50
477908 M 70 63
477908 M 70 51
;

proc sql;

select ID, count (distinct score)

as count

from record

where score>50

group by ID;

quit;

I need some guidance on how to complete this exercise, please help!! Thank you!

[Kurt_Bremser]

a) select gender, count(distinct id) group by gender

b) select id, count(id) group by id where score > 50

c) select id, mean(score), min(score), max(score) group by id

d) select id, mean(score) as score group by id having calculated score > 45

[sustagens · Accepted solution]

/*a*/
proc sql;
create table item_a as
select Gender, count (Gender) as count /*count the gender from the result of the sub query*/
from (select distinct ID, Gender from record) /*sub query to get distinct list of IDs and their gender, since there are multiple entries per ID*/
group by Gender;
quit;

/*b*/
proc sql;
create table item_b as
select ID, count (ID) as count_visits
from record
where score>50
group by ID;
quit;

/*c*/
proc sort data=record;
by ID Score; /*sort first*/
run;

proc means data=record;
var Score; /*we want to analyse score*/
by ID; /*for every value of ID*/
output out=item_c;
run;

/*d*/
proc sql;
create table item_d as 
select ID, Score as Mean 
from item_c /*let's take from the output in item c*/
where _STAT_ = 'Mean' and Score > 45;
quit;

[Reeza]

I could see B being interpreted two ways, visits for patients who had a 50 or above in any visit, or number of visits with 50 and above. To me that one is ambiguous.