topic Re: Creating new variable using proc means in SAS Procedures

Creating new variable using proc means

Mica27 — Wed, 26 Jul 2017 20:23:00 GMT

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.

Re: Creating new variable using proc means

kiranv_ — Wed, 26 Jul 2017 20:27:30 GMT

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;

Re: Creating new variable using proc means

Reeza — Wed, 26 Jul 2017 20:30:05 GMT

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.

Re: Creating new variable using proc means

Mica27 — Wed, 26 Jul 2017 20:44:44 GMT

Thank you very much! I will try this out.

Re: Creating new variable using proc means

Mica27 — Wed, 26 Jul 2017 20:45:54 GMT

Thanks for your help.

Re: Creating new variable using proc means

yanshuai — Wed, 07 Mar 2018 02:33:34 GMT

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.

Re: Creating new variable using proc means

Reeza — Wed, 07 Mar 2018 15:48:25 GMT

@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

Re: Creating new variable using proc means

yanshuai — Wed, 07 Mar 2018 18:54:03 GMT

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.

Re: Creating new variable using proc means

Reeza — Wed, 07 Mar 2018 18:56:26 GMT

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