Hi all: I am terrible at Retain/Lag/Loop, etc. and I am about to give up on my quest.  I have a query that I built out for someone far less experienced with SAS, but now they are asking if I can enhance the query to give them what they want. Ultimately what I am looking to do is to get a date range for each occurrence of Statement codes for a single account.  There can be a break in the continuity of those statement codes, which is making this more difficult, so for each change in the Group code I need the min EFF From Date and the max Eff To Date, therefore needing to create groups, but a single Statement Code could essentially have multiple groups.  I have tried the min and max grouping by the Statement code but that doesn't give me what I am looking for, as it gives me overlaps.  I have attached a sample of the data I have and the data I want.     I have tried the following using Lag to identify when the missing value turns to the T and then back again.  but then I am lost.  each time there is a change that first value for that observation is missing.  What I'd like to do from there is to cut off that first group and call it like Group A, and then starting from the missing value through the next missing value call them Group B, etc etc etc.  Then I can apply the min and maxes that will give me the desired results.  Any thoughts out there in SAS LAND, please tell me there is a solution that I just have no clue about. Additionally, I am hoping that the end user doesn't have to do anything manual, except for enter the account number into the query in order to get to the results.  So no manual manipulation for the end user, like take rows 1 through X and keep those, everything else, pull into a different dataset, then combine down the road. TIA, EIrvin ... View more

I have 4 different tables with a certain metric in each table, and 3 columns in each table, that I am attempting to join on.    the data is as follows:   table 1 Region | Center | Assoc | Metric 1 | Descriptor 1 a            1            1           z             c            3            3           z d            4            4           z   table 2 Region | Center | Assoc | Metric 2 | Descriptor 2 a            1            1            t b            2            2            t c            3            3            t   table 3 Region | Center | Assoc | Metric 3 | Descriptor 3 a            1            1            y b            2            2            y c            3            3            y d            4            4            y   table 4 Region | Center | Assoc | Metric 4 | Descriptor 4 a             1            1           x d             4            4           x     the result that I am looking for would have all regions, centers, and associates listed whether or not they are in 1 or all of the tables.  and all the metrics are filled in, even if its missing.  The important part is that each region, center, and associate that is in any of the 4 tables be shown in the resulting table.   here's what I'm looking for: Region | Center | Assoc | Metric 1 | Descriptor 1 | Metric 2 | Descriptor 2 | Metric 3 | Descriptor 3 | Metric 4 | Descriptor 4 a             1           1           z                                     t                                     y                                    x b             2           2           .                                      t                                     y                                    . c             3           3           z                                     t                                     y                                    . d             4           4           z                                     .                                     y                                    x   I have tried a full join as seen below, and FROM WORK.BC_ACTIVATION_OFFICERS t1 FULL OUTER JOIN WORK.BC_CLOSEDIN90_OFFICERS t2 ON (t1.'Region No'n = t2.'Region No'n) AND (t1.'Center No'n = t2.'Center No'n) AND (t1.Officer = t2.Officer) FULL OUTER JOIN WORK.CLOSED_W_NEW_WITHIN_30D_OFFICERS t3 ON (t1.'Region No'n = t3.'Region No'n) AND (t1.'Center No'n = t3.'Center No'n) AND (t1.Officer = t3.'New Acct Officer'n) FULL OUTER JOIN WORK.BC_APPS_BY_OFFICER t4 ON (t1.'Region No'n = t4.'Region No'n) AND (t1.'Center No'n = t4.'Center No'n) AND (t1.Officer = t4.Officer) and I get missing regions and centers and officers.  I have checked the tables individually, and there are no missing regions, centers or associates in any of them     Ideas?   Thanks, El ... View more

I have a quandry.  Recently I have been told that using the Import Wizard to bring in external tab delimited text files was creating a bunch of temporary files on my C Drive, which I am not allowed to access, and as such is causing a plethora of other issues with my computer.  It was suggested that I begin using Data step to import the external files.  When I have run the suggested code, the file imports, but the display is completely in accurate.  Which makes it difficult to go back and troubleshoot any output issues.  I have tried to copy and paste the Import Wizard code into a program, and it doesn't run at all (because it is still looking for a temporary file).      Sample Data Import Wizard Code - This is the code that doesn't work outside of using the Import Wizard   Code generated by a SAS task Generated on Thursday, July 06, 2017 at 4:37:59 PM By task: Import Data Wizard Source file: G:\Financial Services\Financial Reporting\Monthly Production Reports\Wealth_Core Reports\Retail_Accounts_for_Dashboards 062017.txt Server: Local File System Output data: WORK.Retail_Accounts_for_Dashboards_0 Server: Analytics Note: In preparation for running the following code, the Import Data wizard has used internal routines to transfer the source data file from the local file system to Analytics. There is no SAS code available to represent this action. -------------------------------------------------------------------- */ DATA WORK.Retail_Accounts_for_Dashboards_0; LENGTH 'Account Number'n $ 16 Product $ 3 'Sub Product'n $ 3 'User Field39'n $ 2 'Account Number Dim Key'n $ 11 ; FORMAT 'Account Number'n $CHAR16. Product $CHAR3. 'Sub Product'n $CHAR3. 'User Field39'n $CHAR2. 'Account Number Dim Key'n $CHAR11. ; INFORMAT 'Account Number'n $CHAR16. Product $CHAR3. 'Sub Product'n $CHAR3. 'User Field39'n $CHAR2. 'Account Number Dim Key'n $CHAR11. ; INFILE 'E:\saswork\_TD13452_WIL-ENTSASPRD06_\#LN00033' LRECL=39 ENCODING="WLATIN1" TERMSTR=CRLF DLM='7F'x MISSOVER DSD ; INPUT 'Account Number'n : $CHAR16. Product : $CHAR3. 'Sub Product'n : $CHAR3. 'User Field39'n : $CHAR2. 'Account Number Dim Key'n : $CHAR11. ; RUN;   Suggested Code - the one with the 3-Card monty for column values: DATA Ret_Accts_for_Dashboards; LENGTH 'Account Number'n $ 16 Product $ 3 'Sub Product'n $ 3 'User Field39'n $ 2 'Account Number Dim Key'n $ 11 ; FORMAT 'Account Number'n $CHAR16. Product $CHAR3. 'Sub Product'n $CHAR3. 'User Field39'n $CHAR2. 'Account Number Dim Key'n $CHAR11. ; INFORMAT 'Account Number'n $CHAR16. Product $CHAR3. 'Sub Product'n $CHAR3. 'User Field39'n $CHAR2. 'Account Number Dim Key'n $CHAR11. ; INFILE '\\wil-netapp02\rev-cred\Financial Services\Financial Reporting\Monthly Production Reports\Wealth_Core Reports\06 2017 Retail Interchange.txt' DLM=' ' DSD MISSOVER FIRSTOBS=2; INPUT 'Account Number'n : $CHAR16. Product : $CHAR3. 'Sub Product'n : $CHAR4. 'User Field39'n : $CHAR2. 'Account Number Dim Key'n : $CHAR11.; RUN; And the resulting outputs with the display issues - all of the data is to come in as character, but using Proc Import chooses the 'best' data type for this, which doesn't necessarily work out great.     Any thoughts on how to correct the display issues?   Thanks   El ... View more

Good Day all!   I have hit a quandry that I am having some difficulty finding a workable solution outside of hard-coding dates.  I run a daily report where I compare Month-to-date to Prior Month-to-date and Prior Year Month-to-date. I have succesfully written out global statements to identif these ranges to where when the year rolls over there is no need to change any coding.  However, my quandry is looking at week-to-date and prior week-to-date.  SAS identifies Sunday as the beginning of the week, however, my need is to run the week Monday- Sunday.  In addition to that caveat, I need a solution to keep the beginning of the week at Monday then compare this week to date to last week to date.  For example if today is Wednesday, Current Week-to-date should show the date ranges of this Monday to this Wednesday and the prior week-to-date to show last Monday through last Wednesday.  The dates that the program is pulling is based on dates that are preformatted ddmmmyyyy.  The global statements I have baked into my program work wonderfully and accurately, and they are shown below. /*CURRENT DATE*/ %LET CD=INTNX('DAY',TODAY(),-1); /*PREVIOUS YEAR START DATE*/ %LET PY=INTNX('YEAR',&CD,-1); /*PREVIOUS YEAR MONTH START DATE*/ %LET PYM=INTNX('MONTH',&CD,-12); /*PREVIOUS YEAR TO DATE*/ %LET PYTD=INTNX('YEAR',&CD,-1,'SAMEDAY'); /*CURRENT YEAR START DATE*/ %LET CY=INTNX('YEAR',INTNX('YEAR',&CD,1),-1); /*PREVIOUS MONTH START DATE*/ %LET PM=INTNX('MONTH',&CD,-1); /*PREVIOUS MONTH TO DATE*/ %LET PMTD=INTNX('MONTH',&CD,-1,'SAMEDAY'); /*CURRENT MONTH START DATE*/ %LET CM=INTNX('MONTH',INTNX('MONTH',&CD,1),-1); Any thoughts or suggestions would be greatly appreciated. ... View more