topic Re: Divide variable if the same in SAS Programming

Divide variable if the same

lerdem — Tue, 10 May 2016 14:29:27 GMT

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

Re: Divide variable if the same

lerdem — Tue, 10 May 2016 14:58:02 GMT

nobody knows?

Re: Divide variable if the same

Astounding — Tue, 10 May 2016 15:21:21 GMT

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.

Re: Divide variable if the same

ballardw — Tue, 10 May 2016 17:45:29 GMT

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.

Re: Divide variable if the same

Reeza — Tue, 10 May 2016 16:03:52 GMT

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;

Re: Divide variable if the same

Astounding — Tue, 10 May 2016 22:48:05 GMT

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.