topic Re: generate mean value within subgroup in the original table in SAS Programming

generate mean value within subgroup in the original table

superbibi — Thu, 01 Nov 2018 14:55:26 GMT

Hi Friends,

I want to generate a new column for the mean value within the same ID and the same year. How can I write the sas code without using proc sql?

Thanks.

Data I have

ID	year	value
19450405FCIS	1	143
19450405FCIS	1	125
19450405FCIS	2	128
19450405FCIS	2	137
19600917FNMS	1	133
19600917FNMS	1	143
19600917FNMS	2	119
19600917FNMS	2	122

data I want:

ID	year	value	average
19450405FCIS	1	143	134
19450405FCIS	1	125	134
19450405FCIS	2	128	132.5
19450405FCIS	2	137	132.5
19600917FNMS	1	133	138
19600917FNMS	1	143	138
19600917FNMS	2	119	120.5
19600917FNMS	2	122	120.5

Re: generate mean value within subgroup in the original table

PaigeMiller — Thu, 01 Nov 2018 15:02:45 GMT

@superbibi wrote:

I want to generate a new column for the mean value within the same ID and the same year. How can I write the sas code without using proc sql?

You know, I absolutely despise requirements like "Can't use SQL" when you are in a situation where it is reasonable to use SQL because it is the easiest and most direct approach.

So that's my answer. SQL is the easiest and most direct approach. I'd be happy to provide the actual code if you want to see it.

Re: generate mean value within subgroup in the original table

novinosrin — Thu, 01 Nov 2018 15:10:07 GMT

Good morning @PaigeMiller +1

Hi @superbibi This is the perfect situation/case where you can take advantage of proc sql remerge and also for the reason the dataset doesn't have to sorted by id. This is the reasoning behind this.

Re: generate mean value within subgroup in the original table

RW9 — Thu, 01 Nov 2018 15:14:07 GMT

Do a proc means on the original data, then merge the output from the proc means back onto the original data.

Re: generate mean value within subgroup in the original table

Reeza — Thu, 01 Nov 2018 15:19:43 GMT

Here's a fully worked example.

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

If you're still having issues please post your code and log and explain what type of issues you're having.

@superbibi wrote:

Hi Friends,

I want to generate a new column for the mean value within the same ID and the same year. How can I write the sas code without using proc sql?

Thanks.

Data I have

ID year value

19450405FCIS 1 143

19450405FCIS 1 125

19450405FCIS 2 128

19450405FCIS 2 137

19600917FNMS 1 133

19600917FNMS 1 143

19600917FNMS 2 119

19600917FNMS 2 122

data I want:

ID year value average

19450405FCIS 1 143 134

19450405FCIS 1 125 134

19450405FCIS 2 128 132.5

19450405FCIS 2 137 132.5

19600917FNMS 1 133 138

19600917FNMS 1 143 138

19600917FNMS 2 119 120.5

19600917FNMS 2 122 120.5

Re: generate mean value within subgroup in the original table

kiranv_ — Thu, 01 Nov 2018 15:43:56 GMT

as @PaigeMiller said this is very easy to do in SQl and also @RW9 suggested code is given below. modified code/simplified by using nway as per suggestion of @Reeza. Thanks @reeza

proc means data= have mean nway noprint;
class id year;
output out = have1 mean=/autoname;
run;

proc sort data=have;
by id year;
run;

proc sort data=have1;
by id year;
run;

data want;
merge have have1(keep=id year  value_mean);
by id year;
run;

Re: generate mean value within subgroup in the original table

novinosrin — Thu, 01 Nov 2018 15:32:20 GMT

Something fancy but I will not use this:

data have;
input ID $	year	value;
cards;
19450405FCIS	1	143
19450405FCIS	1	125
19450405FCIS	2	128
19450405FCIS	2	137
19600917FNMS	1	133
19600917FNMS	1	143
19600917FNMS	2	119
19600917FNMS	2	122
;

data _null_;
if _n_=1 then do;
   dcl hash H (ordered: "A",multidata:'y') ;
   h.definekey  ("id",'year') ;
   h.definedata ("id",'year',"value", "average") ;
   h.definedone () ;
end;
call missing(average);
do until(last.year);
set have end=lr;
by id year;
average=mean(value,average);
h.add();
end;
h.replace();
if lr then h.output(dataset:'want');
run;

Re: generate mean value within subgroup in the original table

Reeza — Thu, 01 Nov 2018 15:38:47 GMT

@kiranv_ look at the NWAY option in PROC MEANS or the WAYS to control the TYPE output more explicitly.

Re: generate mean value within subgroup in the original table

novinosrin — Thu, 01 Nov 2018 16:09:37 GMT

Hi @superbibi ---" How can I write the sas code without using proc sql?"

Aside the above doube DOW is another one equivalent of remerge.

That leads to some kind of assumptions that this could perhaps be some interview question by a nut or atleast i have faced so weird syntax questions where once I forgot the syntax, and i was rejected with the feedback I do not know SAS. I was like, oh well thank you.

On the other hand, it could your curiosity to perhaps learn all the possibilities besides sql presumably you are good in sql.

1. Traditional:

notsorted class statement and proc means/summary

sorted- by statement and proc means/summary

2. SQL - remerge

3. One by one datastep on the first pass, calculate avg and then merge back on the original

4. Fancy DOW double

5. Hash

6. Array along with dow -load nums , calc avg and double pass

7. Super fancy APP load with peekc and calc avg and double pass

8. Self imposed interleave mimicking DOW in1, in2 perform avg on in1 and output in2

prolly once my caffeine sets in, i should add more lol

Re: generate mean value within subgroup in the original table

PaigeMiller — Thu, 01 Nov 2018 15:47:07 GMT

@kiranv_ wrote:

as @PaigeMiller said this is very easy to do in SQl and also @RW9 suggested code is given below. modified code/simplified by using nway as per suggestion of @Reeza@. Thanks @Reeza
proc means data= have mean nway noprint;
class id year;
output out = have1 mean=/autoname;
run;

proc sort data=have;
by id year;
run;

proc sort data=have1;
by id year;
run;

data want;
merge have have1(keep=id year  value_mean);
by id year;
run;

Yes, way back in the Cretaceous era, when I was fighting off dinosaur attacks with one hand and programming SAS with the other hand, and there was no PROC SQL in SAS, I did it this way as well. The sort of data set HAVE1 is not necessary.

Re: generate mean value within subgroup in the original table

superbibi — Wed, 07 Nov 2018 14:29:18 GMT

Thank you all. At least I know now proc sql is simpler than another SAS code.