Hi all, I am drawing a blank. I have a dataset that parses a sas log, does some clean up, and ends with a record of what all the user did in their journey from logging in, and clicking on reports/exporting reports. For my testing I have the dataset narrowed down to just what I did on 8/8/2019. This is what the dataset looks like: date time ip action tab report_name count 08/08/2019 14:20:22 xxx.xx.xxx.xxx SASLogon 1 08/08/2019 14:20:56 xxx.xx.xxx.xxx OpenURL University Fall Enrollment by Department.srx 1 08/08/2019 14:21:16 xxx.xx.xxx.xxx OpenURL University New Freshmen by Unit.srx 2 08/08/2019 14:22:09 xxx.xx.xxx.xxx OpenURL Social Work Social Work Graduate Applicants.srx 3 08/08/2019 14:22:12 xxx.xx.xxx.xxx DrillToDetail 1 08/08/2019 14:22:17 xxx.xx.xxx.xxx ExportDetailData 1 My last action in the log was to ExportDetailData. I exported from the Social Work tab and exported the 'Social Work Graduate Applicants.srx' report. I would like to have where action = 'DrillToDetail' and tab = ' ' and report_name = ' ' then tab = the last tab and last report name. Same for ExportDetailData So the table would look like this from my test example, (see red bold below): date time ip action tab report_name count 08/08/2019 14:20:22 xxx.xx.xxx.xxx SASLogon 1 08/08/2019 14:20:56 xxx.xx.xxx.xxx OpenURL University Fall Enrollment by Department.srx 1 08/08/2019 14:21:16 xxx.xx.xxx.xxx OpenURL University New Freshmen by Unit.srx 2 08/08/2019 14:22:09 xxx.xx.xxx.xxx OpenURL Social Work Social Work Graduate Applicants.srx 3 08/08/2019 14:22:12 xxx.xx.xxx.xxx DrillToDetail Social Work Social Work Graduate Applicants.srx 1 08/08/2019 14:22:17 xxx.xx.xxx.xxx ExportDetailData Social Work Social Work Graduate Applicants.srx 1 The tab and report_name can change, especially when I use this with a few hundred users. The actions are coded to handle those 4 actions. Here is the sample code I am working with, I am sure there are more efficient ways to do all of this: /* Parse out the query field to obtain the date, time, ip, group, and report */
data test;
format date MMDDYY10. time time10.;
set ssl_request_logs;
/*if substr(query,1,53) = 'GET /SASWebReportStudio/exportDetailDataOKCommand.do?';*/
if ip = 'xxx.xx.xxx.xxx';
_temp = substr(datetime, 2, lengthn(datetime) - 1);
_temp2=input(_temp,anydtdtm21.);
date=datepart(_temp2);
time=timepart(_temp2);
keep date time ip query;
run;
/*Filter for just my activities for the 8th*/
data test2;
set test;
if date = 21769;
if time >= 51622;
run;
/*output just logon action*/
data logon_action;
format date MMDDYY10. time time10.;
set test2;
if substr(query,1,21) = 'POST /SASLogon/login?';
action = substr(query,7,8);
keep date time ip action;
run;
/*Output reports opened action*/
data reports_opened_action;
retain date time ip tab report_name action;
set test2;
if substr(query,1,37) = 'GET /SASWebReportStudio/openRVUrl.do?';
query = urldecode(urldecode(query)); /* double urldecode takes care of double characters */
query = substr(query,index(query,'Templates') + 9);
report_name = scan(query,1,'%');
tab = scan(report_name,1,'/');
report_name = scan(report_name,1,'(');
report_name = scan(report_name,-1,'/');
action = 'OpenURL';
keep date time ip report_name tab action;
run;
/*Output Drill to Detail Action on the report*/
data drill_to_detail_action;
retain date time ip tab report_name action;
set test2;
if substr(query,1,41) = 'GET /SASWebReportStudio/drillToDetail.do?';
query = urldecode(urldecode(query)); /* double urldecode takes care of double characters */
query = substr(query,index(query,'CMDID') + 5);
action = 'DrillToDetail';
keep date time ip action;
run;
/*Output export detail data action*/
data export_detail_data_action;
retain date time ip tab report_name action;
set test2;
if substr(query,1,53) = 'GET /SASWebReportStudio/exportDetailDataOKCommand.do?';
query = urldecode(urldecode(query)); /* double urldecode takes care of double characters */
query = substr(query,index(query,'APNAME=') + 7);
action = 'ExportDetailData';
keep date time ip action;
run;
/*Merge datasets, create count column*/
data final;
merge logon_action reports_opened_action drill_to_detail_action export_detail_data_action;
by date ip action;
if first.action then count=0;
count+1;
/*if max(last.action);*/
run; Thanks in advance for any help
... View more
