Creating new variable using proc means

[Mica27]

Hello.  I have a data set with person-level wage data.  Each person's record also includes a state, month, and year variable.

I would like to be able to generate the median wage for each state in a given month and then create a new variable with that value. 

Is there a way to do this directly via code and avoid copying from the output window?

I am using SAS 9.3.

Thank you for any help you can provide.

[kiranv_]

you can do this by doing output statement. I have copied an example for you for below link

http://www2.sas.com/proceedings/sugi29/240-29.pdf

PROC MEANS DATA=SUGI.ELEC_ANNUAL NOPRINT;
VAR TOTREV TOTKWH TOTHRS;
OUTPUT OUT=SUGI2 sum(TOTREV TOTKWH) = sum_rev sum_kwh
mean(TOTREV) = mean_rev
median(TOTHRS) = median_hrs;
run;

[Mica27]

Thank you very much!  I will try this out.

[Mica27]

Thanks for your help.

[Reeza · Accepted solution]

https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic

Yes, PROC MEANS has an OUTPUT statement that allows you to capture the output data. 

You may need to tweak the BY statement to get exactly what you want but the follow should help you get started.

proc means data=have noprint;
BY STATE YEAR MONTH;
var wage;
output out=want median(wage) = median_wage;
run;

---

@Mica27 wrote:

Hello.  I have a data set with person-level wage data.  Each person's record also includes a state, month, and year variable.

I would like to be able to generate the median wage for each state in a given month and then create a new variable with that value. 

Is there a way to do this directly via code and avoid copying from the output window?

I am using SAS 9.3.

Thank you for any help you can provide.

---

[yanshuai]

Hello,

can I use CLASS rather than BY command?

And How can I put the median_wage back to the original dataset? In another way, just add another variable containing the value I obtained from this MEANS step.

[Reeza]

---

@yanshuai wrote:

Hello,

can I use CLASS rather than BY command?

---

Try it and see. They are not exact replications of each other, so if you use CLASS you need to make other changes as well.

---

@yanshuai wrote:

And How can I put the median_wage back to the original dataset? In another way, just add another variable containing the value I obtained from this MEANS step.

---

See the examples here:

https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

[yanshuai]

Thank you Reeza!

I have been using SQL or MERGE to do this. But I am trying to find if new variable can be put back to the old table directly without creating new tables.

It seems it is inevitable.

Thank you all the same.

[Reeza]

The last SQL step does it automatically, but if you don't have SAS 9.4+ the median will not be correct.