I have an automated script that run daily that automatically grabs a .tsv log file from a dynamically named file/folder (based upon the date - MM.DD.YY) that automatically creates a png graph with a dynamic name as well also based upon the date (output is name_of_file_MM_DD)..
I love it - works great...
However, other projects want to see the raw data for the graph... Im trying to export this data as part of the automated process and running into some wall.. I can do it manually by exporting the "ALL" spreadsheet from "Output Data" manually without a problem but am running into a problem automating it.. Ultimately, I would like to do BOTH the graph and the spreadsheet - but really need the spreadsheet...
Here is code
******************************************************************;
options obs=10000000;
data _null_;
xx = date();
*yy = dhms(intnx('day',xx,-1),0,0,0);
*yy above is for one day previous ie -1 for midnight ;
yy = dhms(intnx('day',xx, 0),9,0,0);
* Today's date DHMS is day hour minutes seconds;
*zz = dhms(xx,3,0,0);
*zz above is for 3 am changing the 3 to 0 changes it to 12am of today's data;
zz = dhms(xx,21,0,0);
length
this_day this_month this_year $2
this_year4 $4
myfilename $100
;
this_day = put(day(xx),z2.);
this_month = put(month(xx),z2.);
this_year4 = put(year(xx),z4.);
this_year = substr(this_year4,3);
myfilename = 'C:\Prod Ack Gen Time Data\PETE\' !! this_year4 !!'\GZ\' !! put(xx,mmddyyp8.) !! '\core_fed_ack_gen_data' !! this_month !! '_' !! this_day !! '.tsv';
myoutputnm = 'C:\Prod Ack Gen Time Data\PETE\' !! this_year4 !!'\rpt\' !! put(xx,mmddyyp8.) !! '\core_fed_ack_gen_data' !! this_month !! '_' !! this_day !! '.png';
call symput('filepath',strip(myfilename));
call symput('filesout',strip(myoutputnm));
call symput('begdt',"'"!!put(yy,datetime18.1)!!"'dt");
call symput('enddt',"'"!!put(zz,datetime18.1)!!"'dt");
/*
%let begdt = '23Feb16 00:00:00.0'dt ;
%let enddt = '24Feb16 03:00:00.0'dt ;
*/
/*
%let sync_int = 600;
%let ginterval='01:00:00.0't ;
*/
%let sync_int = 60;
%let ginterval='00:15:00.0't ;
run ;
%put filepath=&filepath;
%put filesout=&filesout;
%put begdt=&begdt;
%put enddt=&enddt;
/*
filename filein (
'c:\Users\XBWKB\My Documents\SE\ack\core_0325_00am_fed_data.tsv'
*/
/*
filename filein (
/*
* Note sometimes the time parameter changes
'C:\Prod Ack Gen Time Data\2016\GZ\02.10.16\core_state_ack_gen_data02_10_031502.tsv'
'C:\Prod Ack Gen Time Data\2016\GZ\02.24.16\core_state_ack_gen_data02_24_031500.tsv'
);
*/
filename filein (
"&filepath"
);
/*
'c:\Users\XBWKB\My Documents\SE\ack\core_0325_00am_fed_data.tsv'
'c:\Users\XBWKB\My Documents\SE\ack\core_0325_6am_fed_data.tsv'
'c:\Users\XBWKB\My Documents\SE\ack\core_0316_2days_fed_data.tsv'
'c:\Users\XBWKB\My Documents\SE\ack\core_0316_2days_fed_data.tsv'
'c:\Users\XBWKB\My Documents\SE\ack\core_0224_fed_data.tsv'
'c:\Users\XBWKB\My Documents\SE\ack\core_0224_state_data.tsv'
1040 `A `02/23/2015 19:45:28 `02/23/2015 19:45:49
• Four (4) columns of data as follows:
o submission_type
o Status (accepted/Rejected or RFP)
o receipt time (as date/time)
o ack time (as date/time)
*/
* ie. %sync(ttime,60) ==> '10:00:00't where ttime = '09:59:32't ;
%macro sync (
value ,
interval ) ;
if mod(&value,&interval) >= &interval*.5 then
&value = &interval * int(&value/&interval) + &interval ;
else
&value = &interval * int(&value/&interval) ;
%mend sync ;
data t (keep=int_dt date hour latency type status) ;
infile filein firstobs=1 missover dsd ;
length
tstr
$ 100
p1
p4
type
status
$ 30
;
input tstr ;
format int_dt ack_dt datetime. ;
format rec_date ack_date date. ;
p1 = scan(tstr,3,"`") ;
p4 = scan(tstr,4,"`") ;
type = left(trim(scan(tstr,1,"`"))) ;
status = left(trim(scan(tstr,2,"`")));
* if type =' ' ;
* if status = ' ' ;
Rec_Date = Input(scan(p1,1,' '),mmddyy10.) ;
*Rec_Time = Input(scan(p1,2,' '),time5.) ;
Rec_Time = Input(scan(p1,2,' '),time8.) ;
Ack_Date = Input(scan(p4,1,' '),mmddyy10.) ;
*Ack_Time = Input(scan(p4,2,' '),time5.) ;
Ack_Time = Input(scan(p4,2,' '),time8.) ;
if
Rec_Date = . or
Rec_Time = . or
Ack_Date = . or
Ack_Time = .
then do ;
c+1 ;
*put _all_ ;
delete ;
end ;
int_dt = dhms(Rec_Date, hour(Rec_Time), minute(Rec_Time), second(Rec_Time) ) ;
ack_dt = dhms(Ack_Date, hour(Ack_Time), minute(Ack_Time), second(Ack_Time) ) ;
*latency = (ack_dt - int_dt) / 3600 ;
latency = (ack_dt - int_dt) ;
*if latency <= 75 ; * Weed out outliers ;
If latency = . or latency < 0 then do ;
put 'howdy ' _all_ ;
delete ;
End ;
%sync(int_dt,&sync_int) ;
date = datepart(int_dt) ;
hour = hour(timepart(int_dt)) ;
if int_dt >= &begdt and int_dt < &enddt ;
/*
if int_dt >= '14apr15 18:40:00.0'dt and int_dt < '15apr15 00:10:00.0'dt then delete ;
*/
run ;
*proc print ;
run ;
proc sort ;
by date hour int_dt ;
run ;
*proc print ;
run ;
proc summary data=t ;
by date hour int_dt ;
var latency ;
output out=trec (DROP = _TYPE_ RENAME = (_FREQ_ = receipt_cnt))
mean (latency) = latency_mean_seconds
;
run ;
*proc print ;
*Title 'Receipts and Latency by date hour interval' ;
*sum receipt_cnt ;
run ;
proc summary data=t ;
var latency ;
output out=trec_conf
mean (latency) = latency_mean
lclm (latency) = latency_lclm
uclm (latency) = latency_uclm
stddev (latency) = latency_stddev
p95 (latency) = latency_95th_percentile
;
run ;
proc univariate data=t ;
Title1 'Analysis of Detail Data' ;
title2 ' ' ;
var latency ;
run ;
proc print ;
Title1 'Confidence intervals of Detail Data' ;
title2 ' ' ;
run ;
proc summary data=trec ;
var latency_mean_seconds ;
output out=trec_conf
mean (latency_mean_seconds) = latency_mean
lclm (latency_mean_seconds) = latency_lclm
uclm (latency_mean_seconds) = latency_uclm
stddev (latency_mean_seconds) = latency_stddev
p95 (latency_mean_seconds) = latency_95th_percentile
;
run ;
proc univariate data=trec ;
Title1 "Analysis of &sync_int second sample means" ;
title2 ' ' ;
var latency_mean_seconds ;
run ;
proc print ;
Title1 "Confidence intervals from &sync_int second sample means" ;
title2 ' ' ;
run ;
*Time Receipts IMF Acks BMF Acks FED Acks State Acks Total Acks RFP Total Acks + RFP ;
*02/17/2015 15:00 50 32 2 34 0 34 9 43 ;
data counts ;
*infile 'c:\Users\XBWKB\My Documents\SE\ack\Ack_48_hrs-2-17-15-marci.csv' dsd firstobs=2 missover ;
infile 'C:\Users\d7dmb\Documents\MeF\Prod Scripts\SAS\ack_rate_per_min_48hrs-2-22.csv' dsd firstobs=2 missover ;
drop
p1
counts_time
counts_time2
c
;
length
p1
$ 16
Receipts
IMF_Acks
BMF_Acks
FED_Acks
State_Acks
Total_Acks
RFP
Acks_RFP
8
;
input
p1
Receipts
IMF_Acks
BMF_Acks
FED_Acks
State_Acks
Total_Acks
RFP
Acks_RFP
;
Receipts = Receipts * 60;
IMF_Acks = IMF_Acks * 60;
BMF_Acks = BMF_Acks * 60;
FED_Acks = FED_Acks * 60;
State_Acks = State_Acks * 60;
Total_Acks = Total_Acks * 60;
RFP = RFP * 60;
Acks_RFP = Acks_RFP * 60;
format int_dt datetime. ;
format date date. ;
counts_date = Input(scan(p1,1,' '),mmddyy10.) ;
counts_time = Input(scan(p1,2,' '),time5.) ;
if
counts_date = . or
counts_time = .
then do ;
c+1 ;
*put _all_ ;
delete ;
end ;
int_dt = dhms(counts_date, hour(counts_time), minute(counts_Time), second(counts_Time) ) ;
%sync(int_dt,&sync_int) ;
hour = hour(timepart(int_dt)) ;
date = datepart(int_dt) ;
if int_dt >= &begdt and int_dt < &enddt ;
run ;
*proc print ;
title1 'Initial reads of MeF counts from IBM' ;
run ;
proc sort data=counts;
by date hour int_dt ;
run ;
*proc print ;
run ;
proc summary data=counts ;
by date hour int_dt ;
var
Receipts
IMF_Acks
BMF_Acks
FED_Acks
State_Acks
Total_Acks
RFP
Acks_RFP
;
output out=counts
sum=
;
run ;
*proc print ;
*Title1 'Counts from IBM totaled date hour int_dt' ;
title2 ' ' ;
run ;
data all ;
merge counts(in=incnt) trec(in=intrec);
by date hour int_dt ;
run ;
data all ;
c+1 ;
if c=1 then do ;
set trec_conf ;
retain
latency_lclm
latency_uclm
latency_mean
latency_stddev
latency_stddev_2
latency_95th_percentile
;
latency_uclm = latency_mean + latency_stddev * 2 ;
latency_lclm = latency_mean - latency_stddev * 2 ;
put _all_ ;
end ;
set all ;
drop c _type_ _freq_ ;
run ;
proc print ;
Title 'Final Print after merge' ;
sum
Receipts
IMF_Acks
BMF_Acks
FED_Acks
State_Acks
Total_Acks
RFP
Acks_RFP
receipt_cnt
;
run ;
/**/
*goptions colors=(black blue red);
*symbol1 interpol=spline line=2 width=2 value=triangle c=steelblue; *line=2 dotted;
symbol1 interpol=join width=1 line=2 v=none color=red;
symbol2 interpol=join width=1 line=1 v=none color=black;
symbol3 interpol=join width=1 line=2 v=none color=red;
symbol4 interpol=join width=1 line=1 v=none color=blue;
symbol5 interpol=join width=1 line=1 v=none color=green;
*symbol4 interpol=join width=1 line=1 v=none color=green;
/*
symbol1 interpol=join width=1 v=none color=black;
symbol2 interpol=join width=1 v=none color=blue;
symbol3 interpol=join width=1 v=none color=red;
symbol4 interpol=join width=1 v=none color=green;
*/
run;
* THis is what creates the graph picture automatically;
*ods _all_ close;
ods listing;
* filename grafout "C:\Prod Ack Gen Time Data\2016\GZ\02.24.16\Test.png";
filename grafout (
"&filesout"
);
goptions reset=goptions device=PNG gsfname=grafout;
run;
/* haxis=&begdt to &enddt by &ginterval */
proc gplot data= all ;
format int_dt datetime10. ;
title1 "Submission Processing Control Chart" ;
*title2 "Mean Latency, Receipts " ;
title2 "Period: &begdt to &enddt" ;
title3 "Data grouped in &sync_int Second Intervals" ;
label int_dt = 'Date Time' ;
label latency_mean_seconds= 'latency' ;
/*
Receipts
IMF_Acks
BMF_Acks
FED_Acks
State_Acks
Total_Acks
RFP
Acks_RFP
receipt_cnt
latency_mean_seconds
legend2 order=('Rec' 'Total_Acks');
*/
*legend1 order=('latency_lclm' 'latency_mean_seconds' 'latency_uclm');
*legend1 order=('latency_mean_seconds' 'latency_95th_percentile');
legend1 order=('latency' 'latency_lclm' 'latency_mean' 'latency_uclm');
/*
legend2 order=('Acks_RFP');
*/
legend2 order=('receipt_cnt');
axis1 label=(angle=90 'Latency in Seconds') ;
*legend2 order=('Receipts');
plot
/**/ latency_lclm*int_dt /* */
latency_mean*int_dt
latency_uclm*int_dt
latency_mean_seconds*int_dt
/*** latency_95th_percentile*int_dt ***/
/** latency_uclm*int_dt * */
/ overlay name="tgraph" hminor=0 legend=legend1
haxis=&begdt to &enddt by &ginterval vaxis=axis1
;
/***
plot2 Acks_RFP*int_dt
/ overlay name="tgraph" legend=legend2 ***/
/***/
plot2 receipt_cnt*int_dt
/ overlay name="tgraph" legend=legend2 /***/
;
*by date ;
run;
/*
plot2 Acks_RFP*int_dt Receipts*int_dt
latency_lclm*receipt_dt
latency_uclm*receipt_dt
plot latency_mean_seconds*receipt_dt / name="tgraph" hminor=0
haxis='18feb15:00:00:00'dt to '18feb15:23:59:00'dt by '01:00:00.0't
vaxis=-25 to 40 by 5 ;
plot latency_lclm*receipt_dt / name="tgraph" hminor=0
plot latency_uclm*receipt_dt / name="tgraph" hminor=0
;
*by date ;
plot low*year high*year / overlay
haxis=axis1
hminor=4
vaxis=axis2
vminor=1
caxis=black
areas=2;
run;
*/
Click image to register for webinar
