SAS CREATING GROUPS

[lerdem]

I have output as below:

type        date           building   room   begin     end

event      25mar16    OC-001   101      1200     1600

section   25mar16     OC-001   104      930       1320

event      25mar16    OC-001    114      1230     1600

section    25mar16    OC-002    203     1200    1700

section    25mar16    OC-002    101      1400    1800

event      25mar16     OC-002    204      1000    1130

what i need is

building                room                    end

OC-001        101, 104, 114            1600

OC-002        203, 101,204             1800

[Astounding]

Where does the "1-" or "2-" come from?  Is it the last character in BUILDING or the first character in ROOM?  Do you really need it?  If it comes from BUILDING, could it be 2 or even 3 digits long?

[lerdem]

Actually, it is not really necessary. i can keep only the room numbers. 

[ballardw]

And confirm a rule for selecting the value of End in the output. It could be the max value or in your example data the value associated with room 101 ...

[lerdem]

End is the maximum value for each building. Thanks for help

[LinusH]

Use RETAIN for concatenated room variable and store the max of end within each building (BY).

[Astounding]

To add some detail to Linus's suggestion ... assuming your data is sorted by BUILDING:

data want;

set have;

by building;

length room_list $ 30;

retain room_list end_time;

if first.building then do;

   room_list = room;

   end_time = end;

end;

else do;

   room_list = catx(', ', room_list, room);

   end_time = max(end_time, end);

end;

if last.building;

keep building room_list end_time;

run;

Also, I would suggest that you don't name a variable END.  That is also a SAS statement and the combination can lead to confusing code.

Good luck.

[PGStats]

Perfect fit for a do until() loop :

data have;
input type $ date :date9. building $ room $ begin end;
datalines;
event 25mar16 OC-001 101 1200 1600
section 25mar16 OC-001 104 930 1320
event 25mar16 OC-001 114 1230 1600
section 25mar16 OC-002 203 1200 1700
section 25mar16 OC-002 101 1400 1800
event 25mar16 OC-002 204 1000 1130
;

data want;
length roomList $64;
do until (last.building);
 set have; by building notsorted;
 roomList = catx(", ", roomList, room);
 endTime = max(endTime, end);
 end;
keep building roomList endTime;
rename roomList=room endTime=end;
run;

proc print data=want noobs; run;

[SAS_inquisitive]

@PGStats. Would endtime variable be initialized as zero or missing at the beginning?

endTime = max(endTime, end);

I checked it initializes with missing value. I also wonder how to check what is going on during one complete dow-loop interation? I also read DOW-LOOP does automatic retain, how does it do that?

data want;
	length roomList $64;
     put _all_;
	do until (last.building);
		set have;
		by building notsorted;
		roomList = catx(", ", roomList, room);
		endTime = max(endTime, end);
	end;
	put _all_;

	keep building roomList endTime;
	rename roomList=room endTime=end;
run;

[PGStats]

Variables created within a data step are reset to missing at the beginning of each implicit step iteration. The do until() loops inside a single data step iteration, so it doesn't encounter this automatic reset. After each building group however, roomList and endTime are reset to missing as the data step does its implicit looping. Both functions CATX and MAX handle missing arguments appropriately.