BookmarkSubscribeRSS Feed
lerdem
Quartz | Level 8

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

 

5 REPLIES 5
lerdem
Quartz | Level 8

nobody knows?

 

Astounding
PROC Star

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.

ballardw
Super User

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.

Reeza
Super User

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;
Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1357 views
  • 2 likes
  • 4 in conversation