Morning all,    We are using SAS 9.4 on EG 7.15 within a Citrix environment and have an issue where our work library gets cleared down after a certain amount of idle time (i'd guess it's about 60 minutes).  It's incredibly frustrating to run a piece of code for a few hours, come back to it and see that it's finished, but unable to do anything because no datasets exist anymore.    Is there a setting within SAS or Citrix that is causing this to happen that I can get our IT team to play around with to stop this happening after just one hour?   Thanks Jamie ... View more

Id just like some advice and pointers on ways you would take on the following task:   I use SAS EG and have access to a SAS view on our warehouse.   The table contains a record for every day that an account is open, and contains various information for that account (Date, Account_ID, Outstanding_Balance, Arrears_Amount etc etc).   The original table that this comes from has new records added daily, so it is sorted in date order.  We have rows going back to 2012.   I need to create monthly generation files that contain information for an account where something specific has changed from the day before.  For example, if on 21st February 2017 an Account_ID has Arrears_Amount of 0 and then on the 22nd February that Account_ID has arrears amount of 50, I would want to create a variable called Arrears_Changed and set that to "Y" and then push that row of data into the generational dataset with all other changes that occurred in February 2017.   My challenges are that the dataset is large and not sorted by account_id and that it's a view, so efficiency is really the key here.    Any suggestions greatly appreciated.   ... View more

Hi,    I was wondering if anyone has tried to use the %CODEPOINT2GEOCODE SAS macro (to convert post codes in Great Britain to a usable format) in a locked down Enterprise Guide environment?   We dont have the ability to Pipe files and so the code falls over.     I am able to upload all the appropriate postcode .CSV files to the server using the upload task (location = %let myWorkLocation = %sysfunc(pathname(work));  )     Just curious to see if SAS or anyone else has created an amended version of the macro that would allow read-ins - read-outs to this location using methods that are allowed in EG?   Thanks ... View more

I have a macro variable that holds a string.  The string is a clause that will be used in a procedure, eg; where x = &clause.   The string can hold one or many clauses: Example 1 &clause. = Company A Example 2 &clause. = Company A, Company B, Company C   Does anyone know how I can transform the macro variable to wrap quotes around each of the clauses, so I end up with this:   Example 1 &clause. = "Company A" Example 2 &clause. = "Company A", "Company B", "Company C"    Thanks. ... View more

Morning all,   I have some EG code that starts with a prompt window for the user to select items, then runs through c.10 program nodes, before creating an ODS Excel Output and saving to the network using the Copy Files Add-In.   This is all fine for one off runs of the code, but each month we need to run the code around 50 times manually, selecting a different set of prompts for each run before waiting for the output to be saved to the network and repeating again.   My question is, how would people go about automating this sort of report?  I could have a input file that holds details of the Prompts for each run and have SAS read that in, and then use those inputs to generate th report, but how would you get SAS to go back to the first node of the program?  How would you bypass the prompt selection coming up?  Does it need to be a standalone program in itself, instead of messing with the more manual version of the code?   Just looking for thoughts on the process from anyone who may have had to face into this.   Many thanks Jamie ... View more

I have a program that creates a proc report at the end.  This shows a cohort of data with the variable names in the define statement as 1 to 60 (these are created via a transpose earlier in the process, which is how they are named as a numeric).   However, depending on what criteria a user has selected earlier in the process, it may be there are not 60 months of data to report on.  When this happens, sas obviously falls over.   To get around this, I want to try and identify the maximum number of months data I do have in my dataset (variable name MOB) and when this is less than 60, add a new row with the next MOB number.  Repeat until you have 60 rows of data.   My dataset looks like this...   DTE          MOB          Balance_Remaining 01/01/16  0                0.97 01/01/16  1                0.95 01/01/16  2                0.92 01/01/16  3                0.91 ------ 01/02/16  0                0.95 01/02/16  1                0.89 01/02/16  2                0.82 01/02/16  3                0.77 ------   Does anyone know a simple way of running through the MOB by DTE and adding in a row with a missing Balance_Remaining?  In the above example, the code would need to add from MOB 4 to MOB 60.   Many thanks ... View more

I have a dataset that contains multiple rows per account.  The number of rows = the number of months since an account has been open, with the first row being zero. The table shows how the balance has changed each row (month).  I would like to calculation what the expected balance change should have been had the customer kept to their agreed schedule of payments.    For the first row (where AgeInMonths = 0), the ExpectedBalance should always be equal to the InitialLoanAmount. For subsequent rows, I need to work out what the ExpectedBalance was for the row before and apply an Interest & Repayment calculation to that value to come up with that months ExpectedBalance.  I need to continue doing this until i get to a new AccountNumber where I should start again.   Does anyone know how I would do this?   Thanks Jamie ... View more

I have a proc report outputting through ODS Excel in SAS 9.4.  The report has three across columns and I am trying to have a different background colour for the 3 header rows beneath each of the 3 columns.  I am able to set the background colour for Row 1 with a "style(header)=" statement under the proc report.  And I am also able to change the background colour for the 3rd row by using a "style(header)=" statement within the define statement.   i am struggling however to change the background colour of the cell for the spanned header that is named within the ( ) of the Column statement.   Some things i've seen online suggest that I can use ods escapechar='^'; and then a ^S={background=lightyellow} within the column statement, but that doesn't seem to work.    Does anyone have any suggestions?   here is the code in full:   proc format; value $color "&master_org_name." = '#EDF2F9' "&comp1_report_title." = '#D9D9D9' "All Other &Comparison_group2." = '#F5CFE3' other = '#EDF2F9'; run;   ods escapechar='^'; ods excel file= "temp" options( frozen_headers="3" frozen_rowheaders="1" sheet_name = "Quotes" absolute_column_width='5.43,4.57,9.71,4.57,9.71,4.57,9.71,3.71,8.86,3.71,8.86,6.14,6.14,4,7, 4.57,9.71,4.57,9.71,4.57,9.71,3.71,8.86,3.71,8.86,6.14,6.14,4,7, 4.57,9.71,4.57,9.71,4.57,9.71,3.71,8.86,3.71,8.86,6.14,6.14,4,7' ); PROC REPORT DATA=WORK.PCT LS=164 PS=48 SPLIT="/" NOCENTER HEADLINE style(header)={font_face="Calibri" font_size=9pt background = $color. } style(column)={font_face="Calibri" font_size=8pt background=white} /*style(summary)={background=$color.}*/; options missing = ' '; COLUMN /*APPYRMNTH*/ App_date ("&master_org_name." ("Quotes" MasterDash_AllQuote Masterallquote_val) ("Score Eligible" MASTERDash_ScoreEligible_Quote MasterScore_eligible_val) ("Shawbrook Accept" MasterDash_InitialAgreeRef MasterinitialAgreeRef_Val) ("Applications" MASTERDASH_APP MASTERAPP_VAL) ("Cust. Accept" MASTERDASH_ACCEPT MASTERACCEPT_VAL) ("App 2 Accept Rate" MastVolPct MastValPct) ("Pipeline" MasterPipeline MasterPipeline_val) ) /*(%tslit(All Other &Comparison_group1.) */ (%tslit(&comp1_report_title. ) ('Quotes' Comp1_Dash_AllQuote Comp1_allquote_val ) ('^S={background=lightyellow}Score Eligible ' Comp1_Dash_ScoreEligible_Quote Comp1_Score_eligible_val ) ('^S={background=lightyellow}Shawbrook Accept ' Comp1_Dash_InitialAgreeRef Comp1_initialAgreeRef_Val ) ("Applications" Comp1_DASH_APP Comp1_APP_VAL) ("Cust. Accept" Comp1_DASH_ACCEPT Comp1_ACCEPT_VAL) ("App 2 Accept Rate" Comp1_VolPct Comp1_ValPct) ("Pipeline" Comp1_Pipeline Comp1_Pipeline_val) ) (%tslit(All Other &Comparison_group2.) ("Quotes" Comp2_Dash_AllQuote Comp2_allquote_val) ("Score Eligible" Comp2_Dash_ScoreEligible_Quote Comp2_Score_eligible_val) ("Shawbrook Accept" Comp2_Dash_InitialAgreeRef Comp2_initialAgreeRef_Val) ("Applications" Comp2_DASH_APP Comp2_APP_VAL) ("Cust. Accept" Comp2_DASH_ACCEPT Comp2_ACCEPT_VAL) ("App 2 Accept Rate" Comp2_VolPct Comp2_ValPct) ("Pipeline" Comp2_Pipeline Comp2_Pipeline_val) ) ; DEFINE /*APPYRMNTH*/ App_date / group display WIDTH=5 SPACING=2 RIGHT "Month" order = data format=monyy5.; DEFINE MasterDash_AllQuote / display FORMAT=5. WIDTH=6 RIGHT "Vol" style(header )={background=lightyellow}; DEFINE Masterallquote_val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow}/*style(column)={background=lightyellow}*/; DEFINE MASTERDash_ScoreEligible_Quote / display FORMAT=5. WIDTH=6 RIGHT "Vol" style(header)={background=lightyellow}; DEFINE MasterScore_eligible_val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow}; DEFINE MasterDash_InitialAgreeRef / display FORMAT=5. WIDTH=6 RIGHT "Vol" style(header)={background=lightyellow}; DEFINE MasterinitialAgreeRef_Val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow}; DEFINE MASTERDASH_APP / display FORMAT=5. WIDTH=6 RIGHT "Vol" style(header)={background=lightyellow}; DEFINE MASTERAPP_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow}; DEFINE MASTERDASH_ACCEPT / display FORMAT=5. WIDTH=6 RIGHT "Vol" style(header)={background=lightyellow}; DEFINE MASTERACCEPT_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow}; DEFINE MastVolPct / display style=[tagattr="format:#,###.00%"] WIDTH=6 RIGHT "Vol" style(header)={background=lightyellow}; DEFINE MastValPct / display style=[tagattr="format:#,###.00%"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow}; DEFINE MASTERPipeline / display FORMAT=5. WIDTH=6 RIGHT "Vol" style(header)={background=lightyellow}; DEFINE MASTERPipeline_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" style(header)={background=lightyellow}; 　 DEFINE Comp1_Dash_AllQuote / display FORMAT=5. WIDTH=6 RIGHT "Vol" ; DEFINE Comp1_allquote_val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ; DEFINE Comp1_Dash_ScoreEligible_Quote / display FORMAT=5. WIDTH=6 RIGHT "Vol" ; DEFINE Comp1_Score_eligible_val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ; DEFINE Comp1_Dash_InitialAgreeRef / display FORMAT=5. WIDTH=6 RIGHT "Vol" ; DEFINE Comp1_initialAgreeRef_Val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ; DEFINE Comp1_DASH_APP / display FORMAT=5. WIDTH=6 RIGHT "Vol" ; DEFINE Comp1_APP_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ; DEFINE Comp1_DASH_ACCEPT / display FORMAT=5. WIDTH=6 RIGHT "Vol" ; DEFINE Comp1_ACCEPT_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ; DEFINE Comp1_VolPct / display style=[tagattr="format:#,###.00%"] WIDTH=6 RIGHT "Vol" ; DEFINE Comp1_ValPct / display style=[tagattr="format:#,###.00%"] WIDTH=6 RIGHT "Val" ; DEFINE Comp1_Pipeline / display FORMAT=5. WIDTH=6 RIGHT "Vol" ; DEFINE Comp1_Pipeline_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ; 　 DEFINE Comp2_Dash_AllQuote / display FORMAT=5. WIDTH=6 RIGHT "Vol" ; DEFINE Comp2_allquote_val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ; DEFINE Comp2_Dash_ScoreEligible_Quote / display FORMAT=5. WIDTH=6 RIGHT "Vol" ; DEFINE Comp2_Score_eligible_val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ; DEFINE Comp2_Dash_InitialAgreeRef / display FORMAT=5. WIDTH=6 RIGHT "Vol" ; DEFINE Comp2_initialAgreeRef_Val / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ; DEFINE Comp2_DASH_APP / display FORMAT=5. WIDTH=6 RIGHT "Vol" ; DEFINE Comp2_APP_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ; DEFINE Comp2_DASH_ACCEPT / display FORMAT=5. WIDTH=6 RIGHT "Vol" ; DEFINE Comp2_ACCEPT_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ; DEFINE Comp2_VolPct / display style=[tagattr="format:#,###.00%"] WIDTH=6 RIGHT "Vol" ; DEFINE Comp2_ValPct / display style=[tagattr="format:#,###.00%"] WIDTH=6 RIGHT "Val" ; DEFINE Comp2_Pipeline / display FORMAT=5. WIDTH=6 RIGHT "Vol" ; DEFINE Comp2_Pipeline_VAL / display style=[tagattr="format:£#,##0;[Red]-£#,##0"] WIDTH=6 RIGHT "Val" ; RUN;quit; ... View more