DATA HAVE; LENGTH MONTH_ $5. CATGY DESCR $25. ; INPUT CATGY MONTH_ TOT_CNT DESCR DESCR_PCT MONTHSUM; DATALINES;   CURRENT JAN20 33 STAYED 98 1 CURRENT JAN20 33 ROLLED 2 1 CURRENT FEB20 25 STAYED 2 2 CURRENT FEB20 25 ROLLED 4 2 DEL_30 JAN20 10 STAYED 3 3 DEL_30 JAN20 10 ROLLED 1 3 DEL_30 FEB20 7 STAYED 2 4 DEL_30 FEB20 7 ROLLED 1 4 ; RUN; PROC SQL; CREATE TABLE HAVE2 AS SELECT CATGY,MONTH_,TOT_CNT,DESCR,DESCR_PCT, MONTHSUM FROM HAVE ;QUIT; /*Initial output is here*/ CATGY MONTH_ TOT_CNT DESCR DESCR_PCT MONTHSUM CURRENT JAN20 33 STAYED 98 1 CURRENT JAN20 33 ROLLED 2 1 CURRENT FEB20 25 STAYED 2 2 CURRENT FEB20 25 ROLLED 4 2 DEL_30 JAN20 10 STAYED 3 3 DEL_30 JAN20 10 ROLLED 1 3 DEL_30 FEB20 7 STAYED 2 4 DEL_30 FEB20 7 ROLLED 1 4     Monthsum appears because I need to display based on CATGY and Month The desired output is in the attached spreadsheet because its almost impossible to paste it with the desired format directly I am trying to determine if this can be done with proc transpose, proc tabulate or proc report       ... View more

data have; date1='44204.70833333334'; date2=input(date1,mmddyy10.); run; I recreated this for demonstration purposes. I have a file that I import. The date imports as a character(date1). I need to convert it to a mmddyy10. style date. I attempted to do that in date2 however I get a blank value. Is there a way to convert this character field to a date field ... View more

data test; infile datalines; input Year $ Month $ Dept1N Dept2N ; return; datalines; 2014 1Jun2014 2500 2100 2014 1Jul2014 2330 2220 2014 1Aug2014 1500 2140 2014 1Sep2014 2500 2670 2014 1Oct2014 4500 4100 2014 1Nov2014 2600 7100 ; run; DATA WANT; SET TEST; IF _N_ = NOBS THEN MONTH EQ ''; RUN; I want to say that if MONTH in last row is populated, then show it as a blank.  In this case the last row showing 1NOV2014 would show a blank.  I tried the NOBS function but no success ... View more

proc sql; create table have as select Team, sum(nAtBat) as Tot_Num_Bats, sum(nHits) as Tot_Num_Hits from sashelp.baseball where team in ('Atlanta','Baltimore',' Boston','California','Chicago','Cincinnati',' Cleveland') group by Team having sum(nAtBat) >0 order by Team ;quit; proc report data=have NOWD Wrap style(summary)=Header; columns Team Tot_Num_Bats Tot_Num_Hits Pct_Num_Bats; define Tot_Num_Bats / sum "Total # Bats" ; define Tot_Num_Hits / sum "Total # Hits" ; define Pct_Num_Bats /computed f=percent8.2 "%Hits" ; compute Team; endcomp; rbreak after / summarize style (summary)= Header; compute after; Team = 'Total'; endcomp; run;   I can get the total at the bottom.  How can I get the percentage of the total to show in the far right column (in this case Pct_Num_Bats).  I want to do the same for Tot_Num_Hits ... View more

Please forgive me but I cannot replicate the error I am getting.  This is a general question   processing1 = 'Infosys – Distributed'   this is a snippet of code used in a dataset.  In the dataset processing1 shows the value Infosys - Distributed. However in ODS output the dash is replaced with ? It appears ODS tagset ExcelXP is converting the dash to a ?.   I realized I did not provide a sample however in general is there a way to get sas to show the dash instead? ... View more

proc format; value $mystage 'PROD OPS #:Apps' =  'PROD OPS #: APPS' 'PROD OPS #:Closed' = 'PROD OPS #: CLOSING' 'PROD OPS #:Processing' = 'PROD OPS #: PROCESSING' 'PROD OPS #:Underwriting' =     'PROD OPS #: UNDERWRITING' ; ; run;   data loancount_; set loancount; length stage $85; length processing_center purps_cd $55; if PROCESSING_CENTER eq '' then processing_center = 'NO_LOB';/*2538283*/ if UNDERWRITING_CAMPUS eq '' then UNDERWRITING_CAMPUS = 'NO_LOB';/*2538283*/ if Closing_CAMPUS eq '' then Closing_CAMPUS = 'NO_LOB'; /*2538283*/ stage = put(stage,mystage.); processing_center = put(processing_center,myproc.); purps_cd = put(purps_cd,mypurp.); run;   The report populates however when it encounters the 'PROD OPS #:Underwriting' =     'PROD OPS #: UNDERWRITING' It truncates (leaves off) the g.  It just shows UNDERWRITIN   All other stages display without incident.  When I truncate the space in proc format for Underwriting it displays ok.  Is there a limit on character length?  I realize Underwriting has the longest length in this example. ... View more

data have; length ln Status $25. ; input ln Status Month date9. ; return; datalines; 122 Current 01Nov2019 123 Current 30Nov2019 124 Due_030 01Dec2019 125 Due_030 31Dec2019 ;run; data have2; set have; format month mmyys7.; run; PROC TRANSPOSE data=have2 OUT=have3; BY ln; ID Month; VAR Status /*'ASSESSED FEE VOLUME'n*/; RUN; I have hardcoded 4 values based on Month.  I then do a proc transpose and show 11/2019 and 12/2019.  I need to show these dates in a report.  How can  I account for future dates once the data is populated (ie 1/2020, 2/2020) and avoid the need to hardcode the dates each month?? ... View more

/*This proc export runs and populates without issue*/ proc export data = FINAL OUTFILE= "\\corp\FS\Mortgage\F01\share\RM_Merge\Production\Forb_HST_Misc\MS_Elements_Report_&mToday..xlsb" dbms=EXCELCS replace; SERVER='saspcff'; run;   /*When I attempt to export the same file using .txt as file extension and using pipe delimited it runs however it produces 0 records  Is there an error with code*/ proc export data = FINAL OUTFILE= "\\corp\FS\Mortgage\F01\share\RM_Merge\Production\Forb_HST_Misc\MS_Elements_Report_&mToday..txt" dbms=DLM replace; delimiter='|'; SERVER='saspcff'; run; ... View more

DATA _NULL_; call symput( "Today", COMPRESS(left( put( date(),YYMMDDn8.) ) )) ;/*today for report date exten*/ Run; %PUT &Today; Run; proc sql; create table new1 as select make,model,invoice from sashelp.cars where invoice <=20000 ;quit; /*bring in car_id from libname*/ proc sql; create table cars2 as select a.make,a.model,a.invoice,b.car_id from new1 a inner join gs1.inventory_&Today. ;quit;   The issue is that I need to fetch and use a file called inventory from the gs library.  Even though I am showing a macro (&today) to capture the date,  the actual date in the libname is not consistent each month.  (ie last month the file name was inventory_20200805,  two months ago  inventory_20200702) Is there a way to develop or imporve the existing date macro to capture the exact file regardless of the date extension each month.  This will enable me to automate the run of the report each month ... View more

PROC EXPORT OUTFILE='\\my path\\Capital_Markets_Report_%sysfunc(today(), MMDDYY7.).xlsb'               DATA= FINAL                 label             DBMS= EXCELCS REPLACE;             SHEET='CMREPORT';             SERVER='saspcff'; run;   %let OUTFILE1 = '\\my path\'; %let reportout=Capital_Markets_Report_%sysfunc(today(), MMDDYY7.).xlsb;   filename MI_email email TO= ("myemail@myco.com")  SUBJECT= "Capital Markets Report &rpt_date."  attach = "&OUTFILE1/&Reportout..xlsb";   ERROR: Error opening attachment file '\\mypath\'/Capital_Markets_Report_ 072020.xlsb.xlsb. ERROR: Physical file does not exist, /sas/config9_4/compute/Lev1/SASApp/\\mypath\. NOTE: The SAS System stopped processing this step because of errors. NOTE: DATA statement used (Total process time):       real time           0.00 seconds       cpu time            0.00 seconds   The proc export works fine.  It populated to the path and folder (name changed to hid company name) However  also want to email the same report called Capital markets to an email recipient.  I get the above named error.  It appears the slashes get crossed (//  \\).  Is that the reason for the errors????? ... View more

This code produces the following error proc sql; create table DSHARE_DATA as SELECT LN_NO ,LEGACY_COMPANY ,INITIAL_REQ_DT ,ACCOMODATION_TYPE ,CURR_REQ_CHNL ,INVESTOR_GROUP ,LN_1ST_PRIN_BA ,DEL_BUCKET ,FC_STATUS ,BK_STATUS ,LM_STATUS_CD ,LM_STATUS_CHG_DT ,FB_PLAN_STATUS ,FP_CREAT_DT ,FP_PROMISE_QT ,FP_FST_PR_DU_DT ,FP_LST_PR_DU_DT ,ACCRUAL_STATUS ,PR_ALPHA_STATE_CD ,LN_NXT_PMT_DUE_DT ,ASSISTANCE_STAGE ,CASE WHEN ASSISTANCE_STAGE IN ('FB PLAN NOT SETUP', 'CLIENT CANCELLED ASSISTANCE') THEN 'No' ELSE 'Yes' END AS accom_granted FROM /*D_DataShare.dbo.*/ DATASHAR.Forbearance_HST_Curr UNION SELECT LN_NO ,LEGACY_COMPANY ,INITIAL_REQUEST_DT ,ACCOMMODATION_TYPE ,REQ_CHANNEL ,INVESTOR_INSURER ,LN_1ST_PRIN_BA ,DEL_BUCKET ,FC_STATUS ,BKR_STATUS ,LM_STATUS_CD ,LM_STATUS_CHG_DT ,FP_STAT_CD ,FP_CREAT_DT ,FP_PROMISE_QT ,FP_FST_PR_DU_DT ,FP_LST_PR_DU_DT ,CASE WHEN CA_ACR_STAT_CD = 'ACCRUING' THEN 'ACCRUAL' WHEN CA_ACR_STAT_CD = 'NON ACCRUAL' THEN 'NON-ACCRUAL' ELSE '' END AS ACCRUAL_STATUS /*dif*/ ,PR_ALPHA_STATE_CD ,LN_NXT_PMT_DUE_DT ,ASSISTANCE_STAGE ,CASE WHEN ASSISTANCE_STAGE IN ('FB PLAN NOT SETUP', 'CLIENT CANCELLED ASSISTANCE') THEN 'No' ELSE 'Yes' END AS accom_granted FROM /*D_DataShare.dbo.*/ DATASHAR.Forbearance_HBBT_Curr /*); DISCONNECT FROM DATASHAR;*/ ; QUIT; ERROR: Column 15 from the first contributor of UNION is not the same type as its counterpart from the second.   Does this mean every field before the union must be the exact datatype as after the union??? ... View more

47 proc sql; 48 create table lo as 49 SELECT DISTINCT LO_NAME, 50 Processing_Center_mlcs, 51 APP_DT as Year format mmddyy10., 52 app_dt2 as Month format mmddyy10., 53 Sum(cnt) AS Sum_Lo 54 FROM TBL2_DEDUP_4_ 55 GROUP BY LO_NAME,Processing_Center_mlcs, 56 APP_DT,app_dt2, 57 Year(APP_DT), Year(app_dt2) 58 ORDER BY sum(cnt) DESC 59 /*having sum(cnt)*/ 60 /*ORDER BY Sum(cnt) DESC;*/ 61 ; ERROR: Summary functions are restricted to the SELECT and HAVING clauses only. Why am I getting this error.  I copied the code from MS Access sql ... View more