Filling in blank fields conditionally

[SasPerson85]

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

[ballardw · Accepted solution]

Here is a generic solution to the using the last encountered value to replace missing.

From your code I can't tell where this is appropriate so I am not using any of your data set names or variables

data want;
   set have;
   retain altvarvalue;
   if not missing (varvalue) then altvarvalue=varvalue;
   if missing(varvalue) then varvalue=altvarvalue;
   drop altvarvalue;
run;

You will want to make sure that if your variable of interest is character that the alternate version is defined to have the same length so you don't get truncated data.

You can have multiple variables retained, one for each that you need.