Report using Base SAS techniques

[nrk1787db1]

Hi,

This may be a silly question

Event          Time       

A              1:30

B              1:30

C              1:35

D              1:36

E              1:36

F              1:37

G              1:37

H              1:37

Using the data above I'm trying to generate a report / output as below

If an Event occurred at the same Time as previous Event then report need to list both Events one below the other.

A

B

If an Event occurred at different Time than previous Event then report need to list Events as below

B

   C

Using above conditions full report expected is as below

A              1:30

B              1:30

   C              1:35

      D              1:36

      E              1:36

         F              1:37

         G              1:37

         H              1:37

Can anyone please help ?

[RW9]

I would process it in a datastep before the report section, something like:

data have;
 input Event $ Time $;
datalines;
A 1:30
B 1:30
C 1:35
D 1:36
E 1:36
F 1:37
G 1:37
H 1:37
;
run;
data want (drop=curr_indent);
 set have;
 length curr_indent out_event out_time $200;
 retain curr_indent;
 if _n_=1 then curr_indent="";
 if _n_ ne 1 and lag(time) ne time then curr_indent=cats(curr_indent,"__");
 out_event=tranwrd(cats(curr_indent,event),"_"," ");
 out_time=tranwrd(cats(curr_indent,time),"_"," ");
run;

Now when you get to your report statement, you will need to put asis=on in the options for those columns otherwise the blanks will get removed:

proc report data=want;
  columns event time;
  define event / "Event" style(column)=[asis=on];
  define Time / "Time" style(column)=[asis=on];
...

Oh, and if your doing this within subgroups, you will need a by line in the data want; and instead of if _n_=1, use a if first.bygroup.  (or provide more test data in the form of a datastep with the groups so I can update).

[nrk1787db1]

Thank you RW9

[Astounding · Accepted solution]

You could go really old school and generate a report with PUT statements:

data _null_;

set have;

by time;

retain start -2;

if first.time then start + 3;

file print notitles;

put @start event +10 time;

run;

You can always embellish the report by adding features to the DATA step.  The topic to look up is "Customized Reporting".

[nrk1787db1]

Thank you Astounding for this solution

[dcruik]

Here's a way to do it by creating a data set with multiple columns instead of producing a report (if you want to go that route):

data have;
input Event$ Time$;
datalines;
A 1:30
B 1:30
C 1:35
D 1:36
E 1:36
F 1:37
G 1:37
H 1:37
;
run;

proc sql;
create table unique_time as
select distinct(Time) as Time
from have;
quit;

options mprint mlogic symbolgen;
%macro create;
data _NULL_;
set unique_time end=lastobs;
call symputx(cats("Time",_n_),Time);
If lastobs then call symputx("n",_n_);
run;

%do i=1 %to &n;
	%if &i=1 %then %do;
	data want;
	set have (rename=(Event=Event&i Time=Time&i));
	Where Time&i="&&Time&i";
	run; %end;
	%else %do;
	data time&i;
	set have (rename=(Event=Event&i Time=Time&i));
	Where Time&i="&&Time&i";
	run;

	data want;
	retain Event1-Event&i Time1-Time&i;
	set want time&i;
	run; %end;
%end;
%mend;

%create;
options nomprint nomlogic nosymbolgen;

[nrk1787db1]

Thank you dcruik