DATA Step, Macro, Functions and more

Divide variable if the same

Reply
Contributor
Posts: 47

Divide variable if the same

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

 

Contributor
Posts: 47

Re: Divide variable if the same

nobody knows?

 

Super User
Posts: 5,498

Re: Divide variable if the same

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.

Super User
Posts: 11,343

Re: Divide variable if the same

[ Edited ]

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.

Super User
Posts: 19,772

Re: Divide variable if the same

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;
Super User
Posts: 5,498

Re: Divide variable if the same

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.

Ask a Question
Discussion stats
  • 5 replies
  • 428 views
  • 2 likes
  • 4 in conversation