SAS Support Communities

Have a script that works great   proc import datafile="c:\temp\input.csv" replace /* out command below only keeps what var columns you need for data - missing vars represent empty columns */ out = data_read (keep = var1 var2 var4 var5 var11 var12) dbms=csv out=t2 ; guessingrows=32767 ; *getnames=yes - pulls the header names, only use if csv has headers namerow will also tell you what line to get the names if necessary; getnames=no ; *Firstobs=9; *datarow tells you what line to start; datarow=9 ; DELIMITER=' ' ; run ; proc contents ; run ; proc print ; run ; proc export data=t2 outfile="C:\temp\output.csv" dbms=csv replace ; run ; The problem is that the the input.csv file now contains the following statement    " Elapsed: 00:14:43.61," at the end of the file   So I am looking for way to delete the last line or find a way to delete rows that beging with "elapsed" ... View more

I am using the following code that creates a SAS/Graph jpeg (used to be png) file for all my output from "proc gplot data= all"   * 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=JPEG gsfname=grafout gsfmode=append ; *filename grafout clear; run; my issue is that I have added another gplot to the script, ie "proc gplot data= accesst.all2"  and the second graph is parameterized as " &filestrendout"   What I need is to get an addtional SAS/Graph jpeg (or png) file created (it shows up correctly in my Results tab)   However what ever I try doesn't work (adding &filestrendout to ODS, seperate ODS listing, etc),  i only ever get 1 file and it's "&filesout" data (but it sometime uses the &filestrendout parameter setting).. I suspect my initial ODS listing uses some kind default naming convention and only gets me the "proc gplot data= all" graph, but I need both it and "proc gplot data= accesst.all2" as png or jpeg files..  It can be seperate files, or both graphs can be in the same file - doesn't matter...   Here is "proc gplot data= all"   proc gplot data= all ; format int_dt datetime10. ; *title1 "Submission Processing Control Chart" ; title1 "Federal Latency (PROD) 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; Here is  proc gplot data= accesst.all2   proc gplot data= accesst.all2 ; format int_dt datetime10. ; title1 "Submission Processing Control Chart Trend" ; *title2 "Mean Latency, Receipts " ; title2 "Period: &begdt_tr to &enddt_tr" ; title3 "Data grouped in &sync_int Second Intervals" ; label int_dt = 'Date Time' ; label latency_mean_seconds= 'latency' ; *legend1 order=('latency' 'latency_lclm' 'latency_mean' 'latency_uclm'); legend1 order=('latency'); legend2 order=('Acks_RFP'); axis1 label=(angle=90 'Latency in Seconds') logbase=10 logstyle=expand ; *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_tr to &enddt_tr by &ginterval_tr vaxis=axis1 ; /*** plot2 Acks_RFP*int_dt / overlay name="tgraph" legend=legend2 ***/ ; *by date ; run;  I don't know if it's "tgraph" being used in each or what....  I'm stumped .... Help..     Original script is in https://communities.sas.com/t5/Base-SAS-Programming/SAS-Automation-Export/m-p/315194/highlight/true#M68701   thanks in advance ... View more

I have a scipt - that runs great, it does a proc export to a .csv file file. It's dynamic (see https://communities.sas.com/t5/Base-SAS-Programming/SAS-Automation-Export/td-p/315194) for the full script... My issue is that while it run great, the CSV ouput that is created also creates the data with a tab name that is the same as the outout file name.. So if my output is core_fed_ack_gen_data_12_06_182127.csv then the tab name inside the csv is core_fed_ack_gen_data_12_06_182 (it cuts off a few chars - but no biggie).. What I would like to do is find a way to genericize just the tab name to core_fed_ack_gen_data or even just sheet 1 or whatever...    Thanks in advance   proc export data=ALL outfile="C:\Users\d7dmb\Documents\core_fed_ack_gen_data_12_06_182127.csv" dbms=csv replace ... View more

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; */ ... View more