topic Re: SAS CREATING GROUPS in SAS Programming

SAS CREATING GROUPS

lerdem — Tue, 22 Mar 2016 16:50:47 GMT

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

Re: SAS CREATING GROUPS

Astounding — Tue, 22 Mar 2016 15:40:39 GMT

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?

Re: SAS CREATING GROUPS

ballardw — Tue, 22 Mar 2016 16:37:05 GMT

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 ...

Re: SAS CREATING GROUPS

lerdem — Tue, 22 Mar 2016 16:50:17 GMT

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

Re: SAS CREATING GROUPS

lerdem — Tue, 22 Mar 2016 17:20:21 GMT

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

Re: SAS CREATING GROUPS

LinusH — Tue, 22 Mar 2016 17:30:59 GMT

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

Re: SAS CREATING GROUPS

Astounding — Tue, 22 Mar 2016 17:34:39 GMT

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.

Re: SAS CREATING GROUPS

PGStats — Wed, 23 Mar 2016 03:00:33 GMT

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;

Re: SAS CREATING GROUPS

SAS_inquisitive — Wed, 23 Mar 2016 14:53:09 GMT

@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;

Re: SAS CREATING GROUPS

PGStats — Wed, 23 Mar 2016 15:36:20 GMT

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.