General advice with some tricky SQL questions

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

General advice with some tricky SQL questions

Hello,

I need some advice for an exercise about PROC SQL that I am doing as a prep for an exam. Basically I have been asked to do the following:

  1. Use PROC SQL to find the mean and standard deviation (STD) of age.  Give them variable names and labels, (format if necessary).

    I did it like this (not sure about formats):

    SELECT MEAN(Age_in_Years) as Age_Mean LABEL = 'Age (MEAN)', STD(Age_in_Years) as Age_STD LABEL = 'Age (STD)'
    FROM CreditRating

  2. Use PROC SQL to find the mean and standard deviation of age in each 'personal status and sex' (personalstat) group.

    I did it like this:

    GROUP BY Personal_Status_and_Sex

  3. Add the number in each personalstat group.

    I modified the SELECT clause to add a COUNT(*) as Count like this:

    SELECT MEAN(Age_in_Years) as Age_Mean LABEL = 'Age (MEAN)', STD(Age_in_Years) as Age_STD LABEL = 'Age (STD)', COUNT(*) as Count

  4. Select only the groups with an average age greater than 35.

    I am very confused about this one. I tried something around this:

    HAVING AVG(Age_in_Years)>35;

    But I am not sure if this is the correct answer. Any thoughts?

I am particularly concerned about question D.

Regards,

P.


Accepted Solutions
Solution
‎12-20-2012 07:04 PM
Super Contributor
Posts: 418

Re: General advice with some tricky SQL questions

You have the correct assumption as far as the sql values go, if you assume that the grouping variable Personal_status_and_sex is one variable and not two (sorry I wasn't clear from your email)

proc sql;

create table MYTABLE as

SELECT MEAN(Age_in_Years) as Age_Mean LABEL = 'Age (MEAN)',

STD(Age_in_Years) as Age_STD LABEL = 'Age (STD)'

,count(*) as Count

FROM CreditRating

GROUP BY Personal_Status_and_Sex

having mean(age_in_years) > 35;

quit;

run;


View solution in original post


All Replies
Super Contributor
Posts: 1,636

Re: General advice with some tricky SQL questions

Could you test your code by creating a simple sample data?

Solution
‎12-20-2012 07:04 PM
Super Contributor
Posts: 418

Re: General advice with some tricky SQL questions

You have the correct assumption as far as the sql values go, if you assume that the grouping variable Personal_status_and_sex is one variable and not two (sorry I wasn't clear from your email)

proc sql;

create table MYTABLE as

SELECT MEAN(Age_in_Years) as Age_Mean LABEL = 'Age (MEAN)',

STD(Age_in_Years) as Age_STD LABEL = 'Age (STD)'

,count(*) as Count

FROM CreditRating

GROUP BY Personal_Status_and_Sex

having mean(age_in_years) > 35;

quit;

run;


☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 666 views
  • 0 likes
  • 3 in conversation