topic Re: proc mean with a where clause with median in SAS Programming

proc mean with a where clause with median

GN0001 — Mon, 09 Aug 2021 03:39:39 GMT

input group$ kilograms;

cards;

A 53.4

B 76.3

A 45.3

A 98.4

a 67.3

A 98.3

A 68.3

B 28.4

B 67.3

B 37.4

A 68.3

A 79.2

A 56.4

run;
/*1. Create a new dataset called cleanfinal from cleandata.*/
data cleanfinal;
set cleanme;
run;

2. Set values for the Kilograms variable to between 20 and 40, inclusively.
Data cleanfinal;
set cleanme;
where kilograms between 20 and 40;
Run;

/*3. If values in Kilograms are missing, or not in the 20 to 40 range, 
replace with the median Kilograms value found in Step 2. 
(hint: you will do some conditional logic for Group 'A' and 'B', 
and you will set a median kilograms for A and B)*/

/*This is how I have done it*/
Proc means data=cleanfinal;
where missing (kilograms) or not (kilograms between 20 and 40) median;
var kilograms;
class groups;
run;

Last step gives errors and I don't know how to fix it. I have read many thing about proc mean since morning on and off.

Regards,

blueblue

3. If values in Kilograms are missing, or not in the 20 to 40 range, replace with the median Kilograms value found in Step 2. (hint: you will do some conditional logic for Group 'A' and 'B', and you will set a median kilograms for A and B)

Re: proc mean with a where clause with median

Aku — Mon, 09 Aug 2021 04:21:40 GMT

The problem is that you cannot use where clause inside Proc means. Use it data little bit modified.

Proc means data=cleanfinal(where= (kilograms = .  or not (kilograms between 20 and 40))= median;
var kilograms;
class groups;
run;

Re: proc mean with a where clause with median

andreas_lds — Mon, 09 Aug 2021 05:50:57 GMT

@Aku wrote:

The problem is that you cannot use where clause inside Proc means. Use it data little bit modified.
Proc means data=cleanfinal(where= (kilograms = .  or not (kilograms between 20 and 40))= median;
var kilograms;
class groups;
run;
,

Sorry, but this is wrong. Using where-clause inside proc means is not the problem. The problem is the word "median" at the end of the where statement.

@GN0001 : Please post the full log, so that we see the errors. Also note that the description of the 3rd task is strange:

If values in Kilograms are missing, or not in the 20 to 40 range,

if a value is in the mentioned range, it can't be missing.

Re: proc mean with a where clause with median

PaigeMiller — Mon, 09 Aug 2021 11:28:51 GMT

Your step 1 has no value. Don't do it.

To replace missings with mean, median, sum, range, etc., use PROC STDIZE

proc sort data=cleanfinal;
    by groups;
run;

proc stdize data=cleanfinal(where=(20<=kilograms<=40 or missing(kilograms))) method=median out=cleanfinal1 reponly;    
    by groups;
    var kilograms;
run;

Re: proc mean with a where clause with median

Astounding — Mon, 09 Aug 2021 12:35:49 GMT

As @PaigeMiller rightfully points out, you can automate and combine the last two steps of your assignment. However, for learning purposes, you do need to be able to do each of the steps separately. That being said, you are closer than you think.

First, the problem is not the WHERE clause, and it is not the word MEDIAN. It is the equal sign before the word MEDIAN. Just remove it.

Second, the CLASS statement has to refer to an existing variable. Here, you refer to GROUPS which does not exist in the data. Change it to GROUP and you should be all set.

This assumes that the very beginning of your program actually spells out the name of the data set you want to create, along the lines of:

data cleanme;

It also assumes you are going to take the report of the medians, and type those values back into the program to re-set out-of-range values. You are doing the typing, and not automating the re-setting of out-of-range values.

Re: proc mean with a where clause with median

Tom — Mon, 09 Aug 2021 12:49:37 GMT

I don't understand the assignment.

It mentions using the median found in step 2, but step 2 says nothing about finding a median.

Are you expected to find separate medians for the different groups?

Should those medians be calculated on the full set of values? Or just the values between 20 and 40?

Re: proc mean with a where clause with median

GN0001 — Tue, 10 Aug 2021 18:36:55 GMT

Hello PaigeMiller,

It says:

If values in Kilograms are missing, or not in the 20 to 40 range, 
replace with the median Kilograms value found in Step 2. 
(hint: you will do some conditional logic for Group 'A' and 'B', 
and you will set a median kilograms for A and B

Your where clause says:

where=(20<=kilograms<=40

while the request is not in the 20 to 40 range. Don't we need to add a not prior to where=(20<=kilograms<=40)?

I can understand the question itself. Does it mean give a median for group a and group b when the range is not between 20 and 40?

This is what I have and it doesn't produce correct results:

proc stdize date=cleanfinal1 method=median reponly;
where missing(kilograms) or not (kilograms between 20 and 40);
by group;
var kilograms;
run;

Regards,

blueblue

Re: proc mean with a where clause with median

PaigeMiller — Tue, 10 Aug 2021 18:36:23 GMT

I think you are correct, you would need to add NOT into the code in that case

Re: proc mean with a where clause with median

GN0001 — Tue, 10 Aug 2021 18:51:46 GMT

Hello,

The code doesn't give the correct result though:

Is the question requiring to give a median for each group when kilogram is missing and range is not between 20 and 40?

Please advise me.

I need to take off the out data set to get the cleanfinal1 to have results. please try it.

Also, the result is not correct. please test it.

blueblue

Re: proc mean with a where clause with median

PaigeMiller — Tue, 10 Aug 2021 18:57:18 GMT

proc sort data=cleanfinal;
    by groups;
run;

/* when kilograms < 20 or > 40, set to missing */
data cleanfinal;
    set cleanfinal;
    if kilograms<20 or kilograms>40 then call missing(kilograms);
run;

/* Replace missings with medians of non-missing */
proc stdize data=cleanfinal method=median out=cleanfinal1 reponly;    
    by groups;
    var kilograms;
run;