BookmarkSubscribeRSS Feed
lerdem
Quartz | Level 8

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

9 REPLIES 9
Astounding
PROC Star

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
Quartz | Level 8

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

ballardw
Super User

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
Quartz | Level 8

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

LinusH
Tourmaline | Level 20
Use RETAIN for concatenated room variable and store the max of end within each building (BY).
Data never sleeps
Astounding
PROC Star

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
Opal | Level 21

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;
PG
SAS_inquisitive
Lapis Lazuli | Level 10

@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
Opal | Level 21

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. 

PG

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
  • 9 replies
  • 1569 views
  • 3 likes
  • 6 in conversation