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 ?
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".
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).
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".
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.