Hi all--   I'm trying to write the result of a query to an Excel workbook, but the path I'm using has an underscore (_) in it which is causing the program to error out.  The file path is:   \\Opr.companyname.org\dfs\BANK\00\WORKGROUP\BANK\FCRA_CO\SASoutputsCRCMS\   This is how it resolves in my execution and the error:   ERROR: Invalid file, \\OPR.COMPANYNAME.ORG\DFS\BANK\00\WORKGROUP\BANK\FCR.   Like it is turning the "A_" into a "." and stops processing.   This is on a Windows 10 machine.  I've tried using %str(_) and %bquote, but haven't had any luck. the file will be named &Report_Mth..xlsx.   Can one of the brilliant minds out there help me get past this?  Thank you, ... View more

Hello all,   I have a library containing monthly datasets back to December 2006 and I would like to loop through each of them, perform my data step logic, and then append them together.  I use macros but have never used like this before.   My files are all dated with the following naming convention:  N4.BANK.PORT.NOTEyymm.FILE where yymm=0612 for December 2006.   My initial thoughts were to create macro variables for yy and mm and processes using iterative loops to call each file, for example: %Macro      yy=06;        mm=01 to 12;          DATA PORT_DATA (KEEP=LN_ACCOUNT DER_RPT_DT);      SET PORT&&YY&MM..FILE;      IF SUBSTR(STRIP(LN_ACCOUNT),1,1) = 9;      DER_RPT_DT = PUT((MDY(&MM.,1,&YY.)),MMDDYY10.);      FORMAT DER_RPT_DATE MMDDYY10.;           RUN;            proc append;        next mm;        yy=yy+1;      end;      run; % mend   My problem is that I'm not sure how to implement, or if this is even the right syntax.  Please help me identify a solution!   Regards, Jacob SAS 9.4 ... View more

Hi all, I am trying to count (REL_COUNT) each individual occurence that a customer has a relation (LN_ACCOUNT) as either a borrower (PRI_SSN) or co-borrower (SEC_SSN)  and then apply a flag (0,1) if they are a multiple relationship customer (MUTL_REL).  My code below works only at comparing the borrower for each loan but not if the borrower is also listed as co-borrower (SEC_SSN) on another loan.  For instance using the sample below the REL_COUNT variable would return the folloing for obs 1-6 (1; 1; 2; 1; 1; 1) but my desried out come would be for the REL_COUNT  variable to return the following for obs 1-6 (1; 1; 2; 1; 1; 2).  Is this at all possible? DATA TEST;      INPUT PRI_SSN $ SEC_SSN $ LN_ACCOUNT $ ENTRD_DT;      DATALINES;           29842     29843     0081     12JUL2011           40011     45074     9374     16NOV2012           40011     78118     4970     21SEP2013           41872     21671     9459     05FEB2008           60052     80010     6749     13AUG2013           80010     60052     6231     01JAN2014; RUN; PROC SORT DATA=TEST;      BY PRI_SSN SEC_SSN ENTRD_DT; RUN; DATA B;      SET TEST;      BY PRI_SSN;      MULT_REL = 1;      IF FIRST.PRI_SSN THEN REL_COUNT = 1;      ELSE DO;           IF PRI_SSN = PRI_SSN OR PR_SSN = SEC_SSN THEN REL_COUNT + 1;           ELSE REL_COUNT = 1;      END;      IF LAST.PRI_SSN THEN DO;           IF REL_COUNT > 1 THEN MULT_REL = 1;           ELSE MULT_REL = 0;      END; RUN; Thank you, Jacob ... View more

Hi all, I have a summary report that I am trying to build that groups together Policy Exceptions and then counts the number of request with that exception, the total number of exceptions occuring (because a single request may have multiple exceptions) and the sum of the request amounts for each Exception.  I have a column that I created in my proc sql that creates a unique row id so the first time the request number appears it shows as 1 and for each subsequent exception it would increase (i.e. 2, 3,etc...)  I would like to do a count of the nunmber of requests for each exception where the unique_row_id =1.  Sample data is below. Unique_Row_id Req_No Product Exception Request_Amt Trans_Type 1 308257 Real Estate Insufficient DSC 633239.49 Existing Money 2 308257 Real Estate LTV > 75% 633239.49 Existing Money 1 308475 Real Estate Insufficient DSC 280500.00 New Money 2 308475 Real Estate LTV > 75% 280500.00 New Money 3 308475 Real Estate Other 280500.00 New Money 1 308667 Real Estate Insufficient DSC 238000.00 Existing Money 1 308668 Real Estate Insufficient DSC 192750.00 Existing Money 1 308669 Real Estate Insufficient DSC 256000.00 Existing Money This is how i would like the final report format to be: Policy Exception # of Requests # of Exceptions $ of Exceptiosn New Money Insff. DSC 1 2 $305,500.00 LTV > 75% INV Office 1 1 $280,500.00 New Money Total: 2 3 586,000.00 Existing Money Debt to income > 60% 2 3 $73,691.21 Insuff. DSC 6 8 $3,982,160.72 Existing Money Total: 8 11 $4,088,851.93 Grand Total: 10 14 $4,641,851.93 Any help is greatly appreciated. ... View more