SAS Automation - Export

hondahawkrider — Tue, 29 Nov 2016 14:49:42 GMT

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;

     */

Re: SAS Automation - Export

hondahawkrider — Tue, 29 Nov 2016 15:06:48 GMT

It exports fine manually using

proc export

data=ALL

outfile="C:\temp\filename_ALL.csv"

dbms=csv

replace

;

It's just I am trying to leverage the myfilename and myoutput name values in the same way I get the png..

Re: SAS Automation - Export

Kurt_Bremser — Tue, 29 Nov 2016 15:15:25 GMT

You can use a proper macro variable in the outfile="" option of your proc export.

Re: SAS Automation - Export

ballardw — Tue, 29 Nov 2016 15:17:27 GMT

Why not make the name of the output file in the same step you make your &Filepath as a macro variable? Call it OUTFILEPATH or similar. Just have the file extension match the desired export type.

topic Re: SAS Automation - Export in SAS Programming

SAS Automation - Export

Re: SAS Automation - Export

Re: SAS Automation - Export

Re: SAS Automation - Export