I have an example table as below
id term subj prof hour
20 2016 COM James 4
20 2016 COM Henrey 4
30 2016 HUM Nelly 3
30 2016 HUM John 3
30 2016 HUM Jimmy 3
45 2016 CGS Tim 3
I need to divide hours if the id- term and subj same. There are 2 different prof with same id:20 - term and subj, so i divided hour 2.
There are 3 different prof with same id : 30 - term and subj. So i divided hour 3.
So the output should be like this;
id term subj prof hour
20 2016 COM James 2
20 2016 COM Henrey 2
30 2016 HUM Nelly 1
30 2016 HUM John 1
30 2016 HUM Jimmy 1
45 2016 CGS Tim 3
nobody knows?
There's probably an easier way using SQL, but here's a DATA step approach:
data want;
denom=0;
do until (last.subj);
set have;
by id term subj;
denom + 1;
end;
do until (last.subj);
set have;
by id term subj;
hour = hour / denom;
output;
end;
drop denom;
run;
Dropping DENOM is optional. Required: your data set has to be in sorted order by ID TERM SUBJ.
Is you data already sorted so that all of the values that need the adjustment are together in the data?
Will there be a problem with the result is not an integer? If so, what rounding rule do you need applied.
SQL is pretty straightforward, if you don't mind the remerge note. I usually don't.
data have;
input id term subj $ prof $ hour;
cards;
20 2016 COM James 4
20 2016 COM Henrey 4
30 2016 HUM Nelly 3
30 2016 HUM John 3
30 2016 HUM Jimmy 3
45 2016 CGS Tim 3
;
run;
proc sql;
create table counted as
select *, count(*) as count, hour/calculated count as hour2
from have
group by id, term;
quit;
proc print;run;
Do you need some guidance in how to mark your question as having been answered? That would save time for some people who only want to read questions that haven't been answered yet.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.