data exc_cnt; set loan7; incl= coalesce(APP101,AST102); ln_cnt = 1; if incl ne ''; keep ln_no prog_ctgy gross_ln_amt ln_cnt incl run;   Sample Output Ln_no       prog_ctgy   gross_ln_amt    incl 11111           Doctor      100            101 11122           Doctor      500           102 11133           PVW         100             101 11444           PVM         500             102   I used a proc summary as such proc summary data = exc_cnt /*nway*/;                     class incl ;                   output out = loan7_summ_exc_cd                        sum(GROSS_LN_AMT) = Dollars            sum(ln_cnt) = Units;            run; I get something like this (I removed the _TYPE_ and _FREQ_) Incl      Dollars           UNITS            1200               4 101       200                2 102       1000               2   I need the output to incorporate the prog_ctgy also for something like          Doctor                         PVM            Units  dollars        Units  dollars 101      1          100              1          100 102     1           500            1           500   Also note that incl returns a numeric so I want to transfer to a character and rename it (ie 101 would become Appraisal_101) I am wondering if a proc means would be better or need information on how to get the desired output ... View more

Data Inventory;                               Set Dors_Mtg_Monthly_&Month_Minus_0.                         (keep = ln_no d_portfolio iv_id iv_cat_cd lm_status_cd d_lm_workout_status lm_set_up_dt bkr_status_cd           d_lm_workout_type lm_template_nm d_lm_workout_class                     where = (lm_status_cd = 'A'));                         Days_In_LM = intck('days',lm_set_up_dt,"&EndDate."d);                     If Days_In_LM <= 30 Then                             Day_Band = 'a 0-30 Days   ';                         Else If Days_In_LM <= 60   Then                             Day_Band = 'b 31-60 Days   ';                         Else If Days_In_LM <= 90   Then                             Day_Band = 'c 61-90 Days   ';                         Else If Days_In_LM <= 120 Then                           Day_Band = 'd 91-120 Days ';                         Else If Days_In_LM <= 150 Then                           Day_Band = 'e 121-150 Days ';                         Else If Days_In_LM <= 180 Then                           Day_Band = 'f 151-180 Days ';                         Else If Days_In_LM > 180  Then                             Day_Band = 'g 181 Plus Days';                         if bkr_status_cd = 'A' then                             BK_status = 'Act BK';                           else BK_status = 'Non BK';                         Run;                                                               sample output                             LN_NO Days_In_LM Day_Band BK_status                       0000755850 26 a 0-30 Days Non BK                         0000813774 166 f 151-180 Days Non BK                         0001059013 95 d 91-120 Days Non BK                         0001618131 22 a 0-30 Days Non BK                         0001721380 20 a 0-30 Days Non BK                         0002301380 110 d 91-120 Days Non BK                         0003428588 82 c 61-90 Days Non BK                         0004036448 63 c 61-90 Days Non BK                         0004116703 98 d 91-120 Days Non BK                         0004344529 50 b 31-60 Days Non BK                         0004851473 69 c 61-90 Days Non BK                         0005369905 5 a 0-30 Days Non BK                         0005441282 63 c 61-90 Days Non BK                         0005441647 89 c 61-90 Days Non BK                         0005705017 131 e 121-150 Days Non BK                         0005715362 204 g 181 Plus Days Non BK                                                         The issue is when I apply this piece of code below to rename 'g 181 Plus Days' n to g_181_Plus_Days I get Error 214-322 Variable name ‘g 181 Plus Days’n not valid proc transpose data = Transposed_Days_In_LM_Not_BK                       out = Transposed_Days_In_LM_Not_BK                         (keep = BK_status Total 'g 181 Plus Days'n rename = ('g 181 Plus Days'n = g_181_Plus_Days));               by BK_status;                             id Day_Band;                             var _freq_;                           run;                               ... View more

I have separate datasets located in a libname called test1   Region_20160201 Region_20160202 Region_20160203 Region_20160204 Region_20160205 The numbers represent dates   example datastep: data test1.Region_20160201; run; Here is some sample data for each Region ID         File_Date           Data_Type 11           11/15/05              R 12           05/05/11              T   I want to create a single data-set that combines all of the individual datasets based on the previous month. Therefore we would have this Region_201602 and then have all 5 datasets combined into one dataset. ... View more

I have seen posts regarding use of the date diff function.    1. I need to take a date say a variable called my_date, add 5 years to it  (ie the my_date is always hardcoded on the 1st, 9/1/2010)  5 years from that date is 9/1/2015 2  Now I need to take that resulting date of 9/1/2015 and add 14 days to display if as 9/15/2015 as I always want the 15th of the month 3  Finally I need to subtract 4 months and show a final my_date as 5/15/2015     ... View more

proc sql; create table post_deploy_2 as select SALE_REP_NM,acct_exec_nm,THRD_PARTY_ORIG_NM,THRD_PARTY_ORIG_CD,count(ln_nbr) as Total_Count label='Total' from post_deploy_1 group by SALE_REP_NM,acct_exec_nm,THRD_PARTY_ORIG_NM,THRD_PARTY_ORIG_CD ;quit;   Sample data SALE_REP_NM ACCT_EXEC_NM THRD_PARTY_ORIG_NM THRD_PARTY_ORIG_CD Total_Count   T_M A FINANCIAL MORTGAGE CO KX157 2   T_M R FINANCIAL SERVICES, INC. KX172 1   T_M SL CAPITAL, LLC NVT20 3   T_M SN FIRST BANK CWW81 4 Amber Doe P Smith APD FUNDING CORP NVL84 2 Amber Doe P Smith BR SAVINGS BANK NTK65 2 Amber Doe P Smith CFUND MORTGAGE CORP NVQ59 3 Amber Doe P Smith CTHM, LLC NJL71 1 Jane Doe P Smith FIRST FED BK N491A 5 Jane Doe P Smith KE MORTG NGW93 1 Jane Doe T Andrews PT COMMUNITY BANK NUS07 3 Jane Doe T Andrews SY SECURITIES, INC. NTK73 3 D Nelson T Andrews ACNB BANK N880X 6 D Nelson T Andrews AME MORTGAGE COMPANY EFP24 1 D Nelson T Andrews FI SOUTH BANK NC K724B 1 D Nelson T Andrews GATBANK MORTGAGE, INC KUQ56 1     proc sql; create table post_deploy_3 as select * from post_deploy_2 group by SALE_REP_NM order by Total_Count desc ;quit; My objective is to show the SALE_REP_NM one time and all of their acct_exec_nm, THRD_PARTY_ORIG_CD and Total_Count in descending order Something like this SALE_REP_NM ACCT_EXEC_NM THRD_PARTY_ORIG_NM THRD_PARTY_ORIG_CD Total_Count   T_M SN Capital Bank CWW81 4   T_M SL Capital Bank NVT20 3   T_M A Financial mortgate KX157 2   T_M R Financial KX172 1 Jane Doe P Smith FIRST FED BK N491A 5 Amber Doe P Smith CFUND MORTGAGE CORP NVQ59 3 Amber Doe P Smith APD FUNDING CORP NVL84 2 Amber Doe P Smith BR SAVINGS BANK NTK65 2 Amber Doe P Smith CTHM, LLC NJL71 1 I am attempting to sort in total_count descending order and by sales rep in alpha order.  The present code tends to repeat the sale_rep_nm in different parts of the report.  I am attempting a “rollup” type report with the sale_rep_nm showing just once   ... View more

data _null_; LMTE=intnx('Month',&pbd_date,-1,'b');  CALL SYMPUT('BegDate', PUT( LMTE,date9.)); run; %put &BegDate;   This date macro resolves to 01NOV2015 which is the first day of last month.  Note &pbd_date is a calendar defined variable that assigns the current date, in this case -1 month   Now when I use &BegDate in the script such as   data Decisioned_In_Time_  (keep = ln_no                                                 d_lm_workout_type                                                                                                 lm_referral_cd                                                 ln_lol_msg_xx                                                 lol_rec_chng_dt                                                 bkr_status_cd                                                 bkr_setup_dt                                                 bkr_removal_dt                                                 _385_to_Decision /*                                              Trial_Doc_Prep_to_Street*/ /*                                              Final_Doc_Prep_to_Street*/ /*                                              Tri_or_Fin_Doc_to_Str_3pl_days*/                                                 d_lm_reject_fail_dt                                                 lm_removal_dt                                                 lm_status_cd)                                                 ;      retain ln_no                 Critical_Step                 Critical_nm                                 lm_removal_dt;      merge      Dors_Mtg_daily (where = (d_lm_negotiation_dt ne .                                                                 and not(index(d_lm_workout_status,'NEGOTIATION'))                                                                 and (d_lm_rp_fb_to_doc_prep_dt >= &BegDate.                                                                      or d_lm_qa_trial_dt >= &BegDate.                                                                       or d_lm_workout_approved_dt >= &BegDate.                                                                      or d_lm_trial_doc_prep_qa_dt >= &BegDate.                                                                      or d_lm_trial_on_street_dt >= &BegDate.                                                                      or d_lm_verbal_accept_dt >= &BegDate.                                                                      or d_lm_workout_trial_start_dt >= &BegDate.                                                                      or d_lm_final_review_dt >= &BegDate.                                                                      or d_lm_qa_final_dt >= &BegDate.                                                                      or d_lm_final_approved_dt >= &BegDate.                                                                      or d_lm_final_doc_prep_qa_dt >= &BegDate.                                                                      or d_lm_final_on_street_dt >= &BegDate.                                                                      or d_lm_settled_pend_funds_dt >= &BegDate.                                                                      or d_lm_workout_fulfill_dt >= &BegDate.                                                                      or d_lm_reject_fail_dt >= &BegDate.                                                                      or lm_removal_dt >= &BegDate.) run; NOTE: Line generated by the macro variable "BEGDATE". 01NOV2015          -------          22          76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE, LT, NE, OR, ^=,               |, ||, ~=.    ERROR 76-322: Syntax error, statement will be ignored.   It appears sas is attempting to convert the date into a character.  I have no idea why ... View more