How do I do this without PROC SQL?

HB — Tue, 13 Jan 2015 21:37:26 GMT

* basic options;

OPTIONS MISSING=' ' SORTPGM=BEST ERRORS=1 LINESIZE=132 CENTER

PAGESIZE=42 NUMBER OBS=MAX;

data last_period;

input id hours;

cards;

1 1

2 1

3 1

4 2

5 3

6 3

;

data this_period;

input id hours;

cards;

1 1

2 2

3 3

5 5

6 5

;

* I want to find the average gain in hours for each id that shows up in both files;

* for this data, ID 1 has gained 0 hours from last period to this period, ID 2 has gained 1 hours, ID 3 has gained 2 hours;

* 4 is not in the second file and should be ignored;

* ID's 5 and 6 have both gained 2 hours;

* average gain should be ( 0 + 1 + 2 + 2 + 2 ) / 5 = 1.4

* using PROC SQL I can do;

proc sql;

create table average_gain as

select avg(b.hours-a.hours) as hours_gained

from last_period a inner join

this_period b on

a.id = b.id;

quit;

proc print data=average_gain noobs;

var hours_gained;

title1 'Average additonal hours gained by PROC SQL';

run;

* this works perfectly and, exactly as predicted, prints out 1.4;

* I want to do the same thing without PROC SQL;

* I think I should be able to do better than the following;

proc sort data=last_period;

by id;

run;

proc sort data=this_period;

by id;

run;

data this_period2;

set this_period;

keep id hours1;

hours1 = hours;

run;

data average_gain2;

merge last_period (in = x) this_period2 (in = y);

by id;

if x and y;

run;

data average_gain3;

set average_gain2;

hours_gained = hours1 - hours;

run;

proc means data= average_gain3 mean;

var hours_gained;

title1 'Average additonal hours gained by PROC MEANS';

run;

* prints out 1.4000000

* Am I doing this efficiently? How would you do it? Any ideas for me?;

Re: How do I do this without PROC SQL?

ballardw — Tue, 13 Jan 2015 21:54:41 GMT

data combined;

merge last_period (in=x) this_period(in=y rename=(hours=hours1));

by id;

if x and y;

hours_gained=hours1-hours;

run;

then proc means;

Re: How do I do this without PROC SQL?

HB — Tue, 13 Jan 2015 22:00:40 GMT

Okay. That's a bunch better.

I woudl have never figured out this part:

this_period(in=y rename=(hours=hours1));

topic Re: How do I do this without PROC SQL? in SAS Procedures

How do I do this without PROC SQL?

Re: How do I do this without PROC SQL?

Re: How do I do this without PROC SQL?