data uw_hist_1; set uw_hist; decision_date1=datepart(decision_date); decision_date2=datepart(decision_date); format decision_date1 mmddyy10.; put decision_date2 hhmm8.2; run;   LNKEY DECISION_DATE decision_date1 decision_date2 4001532474 24Sep2016 7:24:00.000 09/24/2016 20721 4001474487 29Sep2016 16:52:00.000 09/29/2016 20726 4001474487 27Oct2016 18:11:00.000 10/27/2016 20754 4001532474 28Nov2016 14:24:00.000 11/28/2016 20786   I want to display the time for decision_date2 in time format like this I used hhmm8.2 however I get different results in decision_date2.  How can I obtain the time format and would it maintain once I exported, especially in ODS whcih is the ultimate output ... View more

data test(keep=ln_no,upd_date); set test2; run; sample data ln_no       upd_date 1111       6/15/14 1122       9/5/11    1122       9/11/15   (repeated) 1444      8/15/11 1166      2/8/11 1166    12/5/15   (repeated)   In ODS is there a way to place a highlight color say orange or red to highlight the ln_no column if the ln_no repeats on the next line. In this case 1122 and 1166 repeated and I want to identify them as repeats.  1122 1166 ... View more

data test(keep=contg_mult); set test1; run;     Sample Data contg_mult 5000 10000 150   In ods I have this DEFINE contg_mult / Display style(column)={tagattr="format:00000.00" cellwidth=80pt just=right} "Contingency Percent";   I am attempting to display as contg_mult 50.00 100.00 1.50 with no percentage sign.   Can I achieve this desired format in ods or is there a way to do this in the dataset itself   ... View more

USER_ID EMAIL LAST_LOGIN USER_TYPE SECURITY_GROUP IS_ACTIVE ACCOUNT_LOCKED ACCOUNT_INACTIVE xxxx john.B.Iwoa@me.com 13Dec2016 12:01:00.000 1 LOS Y N N   eeee john.B.Iwoa@me.com 13Dec2016 11:02:00.000 1 LOS Y N N       proc export data=Los_Prod_Logins outfile="\\Corp\DFS\Mortgage_Shared\VA019\F01B\Everyone\MBIO_ADHOC_SHARED\Emp_Los_Prod\Dly_Loss_Prod_Login_&rptdate.xlsb" dbms=ExcelCS replace; server='GA016A744';   it exports to a shared drive as Dly_Loss_Prod_Login_20161213 where &rptdate reporesents the date However when I export, I loose the datetime format which is needed for this particular report. I tried xls, xlsx, and xlsb format Would I have to go with an ODS output delivery or is there a way to export directly to a share drive, keeping the datetime format. In this case LAST_LOGIN   ... View more

data cars(keep= make type msrp cnt); set sashelp.cars; if make in ('Acura','BMW'); cnt=1; run; output Make Type MSRP cnt Acura SUV $36,945 1 Acura Sedan $23,820 1 Acura Sedan $26,990 1 Acura Sedan $33,195 1 Acura Sedan $43,755 1 Acura Sedan $46,100 1 Acura Sports $89,765 1 BMW SUV $37,000 1 BMW SUV $52,195 1 BMW Sedan $28,495 1 BMW Sedan $30,795 1 BMW Sedan $37,995 1 BMW Sedan $30,245 1 BMW Sedan $35,495 1 BMW Sedan $36,995 1 BMW Sedan $37,245 1 BMW Sedan $39,995 1 BMW Sedan $44,295 1 BMW Sedan $44,995 1 BMW Sedan $54,995 1 BMW Sedan $69,195 1 BMW Sedan $73,195 1 BMW Sports $48,195 1 BMW Sports $56,595 1 BMW Sports $33,895 1 BMW Sports $41,045 1 BMW Wagon $32,845 1   present proc tabulate Proc tabulate data=cars order= data format=10. S=[cellwidth=80]; Class make type; Var msrp cnt; Table make=' ' all={label='Total' S=[background = lightblue cellwidth=80]} *[STYLE=[Font_Weight=BOLD]], type*(cnt=''*sum='Count of Units' cnt=''*colpctn='% of Units'*f=number8.2 msrp=''*sum='MSRP$' *f=dollar16.2 msrp='' *colpctSum='% of MSRP'*f=number8.2 ) all={ label='Grand Total' S=[background = lightblue]} *[STYLE=[Font_Weight=BOLD]] *(cnt =' '*sum='Total Exceptions Units' msrp =' '*sum='Total MSRP Dollars$' *f=dollar16.2 cnt=''*colpctn='Total % in Units' *f=number8.2 msrp=''*colpctSum='Total % in Dollars' *f=number8.2) / box='Car Summary'; label colpctn = '% of Units'; keylabel n=''; run; output Car Summary Type Grand Total SUV Sedan Sports Wagon Count of Units % of Units MSRP$ % of MSRP Count of Units % of Units MSRP$ % of MSRP Count of Units % of Units MSRP$ % of MSRP Count of Units % of Units MSRP$ % of MSRP Total Exceptions Units Total MSRP Dollars$ Total % in Units Total % in Dollars Acura 1 33.33 $36,945.00 29.29 5 27.78 $173,860.00 23.56 1 20 $89,765.00 33.31 . . . . 7 $300,570.00 25.93 25.77 BMW 2 66.67 $89,195.00 70.71 13 72.22 $563,935.00 76.44 4 80 $179,730.00 66.69 1 100 $32,845.00 100 20 $865,705.00 74.07 74.23 Total 3 100 $126,140.00 100 18 100 $737,795.00 100 5 100 $269,495.00 100 1 100 $32,845.00 100 27 $1,166,275.00 100 100 Totals show at the bottom.  Is there a way to arrange the proc tabulate code to display the totals at the top instead   SUV Sedan Sports Wagon         Car Summary   Grand Total Total Units 3           Total Dollars $126,1400                       Count of Units % of Units MSRP$ % of MSRP Count of Units % of Units MSRP$ % of MSRP Count of Units % of Units MSRP$ % of MSRP Count of Units % of Units MSRP$ % of MSRP Total Exceptions Units Total MSRP Dollars$ Total % in Units Total % in Dollars Acura 1 33.33 $36,945.00 29.29 5 27.78 $173,860.00 23.56 1 20 $89,765.00 33.31 . . . . 7 $300,570.00 25.93 25.77 BMW 2 66.67 $89,195.00 70.71 13 72.22 $563,935.00 76.44 4 80 $179,730.00 66.69 1 100 $32,845.00 100 20 $865,705.00 74.07 74.23 And the same across the page ... View more

proc sql; create table exc_cnt as select prog_ctgy,CODEDESC,count(ln_cnt) as cnt_cd,            sum(GROSS_LN_AMT) as gr_amt format=dollar12.2 ,            GROSS_LN_AMT/sum(GROSS_LN_AMT)*100 as amt_pct            from loan7 where CODEDESC ne '' group by prog_ctgy ,CODEDESC having cnt_cd ;quit; Output issue with aggregates  how would i structure the code to avoid the duplicates prog_ctgy CODEDESC cnt_cd gr_amt amt_pct Doctor 101 - Appraisal / Property 12 $7,640,316.00 7.866166792 Doctor 101 - Appraisal / Property 12 $7,640,316.00 11.79925019 Doctor 101 - Appraisal / Property 12 $7,640,316.00 11.77830865 Doctor 101 - Appraisal / Property 12 $7,640,316.00 12.89724142 Doctor 101 - Appraisal / Property 12 $7,640,316.00 6.904164697 Doctor 101 - Appraisal / Property 12 $7,640,316.00 5.456580592 Doctor 101 - Appraisal / Property 12 $7,640,316.00 8.391734059 Doctor 101 - Appraisal / Property 12 $7,640,316.00 3.560062175 Doctor 101 - Appraisal / Property 12 $7,640,316.00 10.54172367 Doctor 101 - Appraisal / Property 12 $7,640,316.00 9.829436374 Doctor 101 - Appraisal / Property 12 $7,640,316.00 8.48852325 Doctor 101 - Appraisal / Property 12 $7,640,316.00 2.486808137 Doctor 102 - Assets 65 $42,424,386.70 2.003564615 Doctor 102 - Assets 65 $42,424,386.70 2.467920179 Doctor 102 - Assets 65 $42,424,386.70 1.004643869 Doctor 102 - Assets 65 $42,424,386.70 1.912433068 Doctor 102 - Assets 65 $42,424,386.70 0.398209646 Doctor 102 - Assets 65 $42,424,386.70 0.777383071 Doctor 102 - Assets 65 $42,424,386.70 2.060135851 Doctor 102 - Assets 65 $42,424,386.70 1.293831314 ... View more

Proc tabulate data=exc_cnt order= data format=10. S=[cellwidth=150]; Class prog_ctgy CODEDESC; Var gr_amt cnt_cd; Table CODEDESC=' ' all={label='Total' S=[background = lightblue cellwidth=160]} *[STYLE=[Font_Weight=BOLD]], prog_ctgy*(cnt_cd=''*sum='Exceptions Units' gr_amt=''*sum='Exceptions$' /** colpctn*/ ) all={label='Grand Total' S=[background = lightblue]} *[STYLE=[Font_Weight=BOLD]] *cnt_cd =' '*sum=' ' / box='Exception Codes'; TITLE 'Summary'; footnote1 &rptdate;   run; This code produced the dataset enclosed in the attachment.  Now I want to add a column percentage total per exception code, grouped by prg_catg.  AS you can see I used colpctn however unable to get it to work in the individual columns.  It need to be a separate measure based on gross loan amount and the number of cnt_cd, shown in the VAR section.   The yellow portion represents where I want to put the data. ... View more