proc sql; create table test1 as select state, ln_no, mod_in_yr, balance from test ;quit; OUTPUT State    ln_no    mod_in_yr          balance   AR       100            Y                    100  CA        102            Y                    200 CA         104            Y                    200   DE        103            N                   100 DE          106            N                   100 DE          107           N                    100                  proc sql; create table my_summ as select State, mod_in_yr,         case when mod_in_yr = 'N' then sum(balance) = 0 else sum(balance) end as mysum,         case when mod_in_yr = 'N' then count(ln_no) = 0 else count(ln_no)  end as mycount where mod_in_yr = 'Y' ;quit; DESIRED OUTPUT State  mysum     mycount AR       100             1 CA       400             2 DE        0                0        I want to show 0 for DE since it did not meet the mod_in_yr requirements however I do not want the code to skip it, just apply a 0 and show it         Note ln_no is a char variable     ... View more

data test(keep=State Amount); set test2; run; Output example State  Amount AR     150 CA     7   Presently if a State has no entries, the code skips to the next variable I want the results to handle missing values and place a zero in amount AL      0     (so I want to show a 0 for AL even though there were no amounts for AL) AR     150 CA      7 ... View more

data _NULL_; month = month(input("&sysdate9",date9.)); year = year(input("&sysdate9",date9.)); call symput('month',compress(put(month,best.))); call symput('year',compress(put(year,best.))); run; Output is 44 %put &year; SYMBOLGEN: Macro variable YEAR resolves to 2017 2017 45 %put &month; SYMBOLGEN: Macro variable MONTH resolves to 11 Based on this month of November and this year. I have a report that will require me to go back several quarters and years.  I have a start up program that will give me the desired time period so lets say I need to not only pull November 2017 but I need to pull another date, say June 30, 2017?  How can I put in code like above to get me the desired date.  Quarterly would be even better (ie jan-mar=qtr1,  apr-jun=qtr2 etx ... View more

data test; infile datalines; input ctr $ cnt ; return; datalines; 4411 5 4455 15 4466 6 4477 11 ; run;   proc report = test style(summary)=Header; title 'test'; column ctr cnt PctTot; define ctr /order order=data style (column)=Header; define cnt /sum f=comma6.; define PctTot ????????????? compute after ctr ; line ' '; endcomp; run;   I am attempting to do the following:   Desired Output Title ‘ test’ Ctr                         Cnt                 PctTot 4411                      5                   13.51%      4455                      15                 40.54%    4466                       6                  16.21% 4477                       11                 29.72%    Grand Total            37                100.00   I need to round the PctTot   2 decimal places and include lines as well as a Grand Total ... View more

The code below exports to a server location.  When I open the spreadsheet it defaults to the bottom of the page.  Is there a way to programmatically default to home or (cell A2)   PROC EXPORT OUTFILE="\\corp\server1\Folder1\Non_QM_&mToday..xlsb" DATA= tbl1 DBMS= EXCELCS REPLACE; SHEET='Data'; SERVER='GA016A744'; quit;     ... View more

data tbl1; set tb; run;   PROC EXPORT OUTFILE="\\corp\dfs\Mortgage_Shared\VA019\F01B\Everyone\MBIO\Non_QM..xlsb" DATA= tbl1 DBMS= EXCELCS REPLACE; SHEET='Data'; SERVER='GA0122'; quit;   The results go to the above named server location however the report carraige return results go to the bottom of the page where there are no records.  I then have to move the scroller to the top.  Is there a way to insert code to have the results begin at the top left or cell (A1) and have a frozen header beginning at cell A2 ??  Or must this be handled in the datastep of "tbl1" ... View more

options missing=' ' topmargin=0.30in bottommargin=0.01in rightmargin=0.25in leftmargin=0.25in; ods tagsets.ExcelXP file= '/sasdata/sasusers/sunt_smith/Mortgage_Recpature.xls'; ODS TAGSETS.ExcelXP options(sheet_interval='none' default_column_width='9' sheet_name="SUMMARY" center_horizontal="no" frozen_headers = "Yes" Orientation='Landscape' embedded_titles='Yes' fittopage="No" blackandwhite="No" Embedded_Footnotes='Yes' absolute_column_width='9' autofit_height="Yes"); PROC REPORT DATA= jaj.Mtg_stm_tot_cap_C1 style(summary)=Header; title 'STM_TOT_CAPTURE %'; COLUMNS mdy numMorts numloans capN gla_l gla_r capD; define mdy / order order=data style (column)=Header; define numMorts / "STM Loans"; define numloans / "Competitor Loans"; define capN / f=number7.1'%' "STM Tot Cap#"; define gla_l / f=dollar15. "STM $"; define gla_r / f=dollar15. "Competitor $"; define capD / f=number7.1'%' "Tot Cap$"; rbreak after /summarize;*/; endcomp; compute after mdy; endcomp; label mdy = 'Month/Yr'; run; /*above code runs fine.  Error generated starting here see bottom*/ title1 'STM Total Mortgage Capture Rate'; title2 '3 Month Moving Average'; proc sgplot data=jaj.Mtg_stm_tot_cap_C1; series x=mdy y=CapN_Avg / markers lineattrs=(color=vibg) markerattrs=(color=vibg symbol=circlefilled) markerattrs=(color=vibg symbol=circlefilled) ; series x=mdy y=CapD_Avg / markers lineattrs=(color=red) markerattrs=(color=red symbol=circlefilled) markerattrs=(color=red symbol=circlefilled) ; label CapN_Avg = "%3MoCap#"; label CapD_Avg = "%3MoCap$"; run;   I am attempting to add in the proc sgplot here in the code.  However when I add in the proc sgplot I get the following error Warning GPATH or PATH is not writable directory Error: Cannot write image to /sasdata/sas94/config/Lev1/SASAPP/SGplot34.png I read up on adding gpath and path however the output is in excel not html or xml.  The examples I have seen use HTML.  How can I get sgplot to work in ODS for excel?? ... View more

data test; infile datalines; input Rec $ RptDate date9. Sales Bonus ; format rptdate date9.; tot=sum(of sales,bonus); return; datalines; 225 1Jun2014 5 2 226 1Jul2014 2 2 227 1Aug2014 1 3 228 1Sep2014 4 2 229 1Oct2014 5 1 230 1Nov2014 6 2 ; run; 　 proc sort =test out=test2; by Rec RptDate; run; 　 proc expand =test2 out=test3; id RptDate; by Rec; convert tot=tot_avg/ transformout=(movave 3 trimleft 2); run;   My proc expand for some reason will not produce the 3 month tot_avg which is based on the sum of sales + bonus. I am attempting a 3 month moving average column   I get this instead REC  RptDate tot_avg  Sales  Bonus    Tot 225 01JUN2014         5       2        7 226 01JUL2014         2       2        4 227 01AUG2014         1       3        4 228 01SEP2014         4       2        6 229 01OCT2014         5       1        6 230 01NOV2014         6       2        8 ... View more

data test; infile datalines; input Year $ Month $ Sales Returns ; return; datalines; 2014 Jan2014 1 1 2014 Feb2014 1 2 2014 Mar2014 1 3 2015 Apr2015 2 2 2015 May2015 1 1 2015 Jun2015 1 1 ; run; proc report = test style(summary)=Header; column Year Month Sales Returns ; define Year /order order=data style (column)=Header; define Sales /sum f=comma6.; define Returns /sum f=comma6.; break after year /summarize; compute after Year; line ' '; endcomp; run; I want to get group totals every 3 months as follows: 1. Add sales to returns (sales+returns) 2. Divide Sales into the (sales+returns) figure and get a percentage called ActivityPct in 3 month intervals (example 3+6=9   then 3/9 = 33% In this example I evaluate after Year.  However for the 3 month totalling I imagine I need a measure that evaluates after 3 months Current Output Year Month Sales Returns 2014 Jan2014 1 1   Feb2014 1 2   Mar2014 1 3 2014   3 6   2015 Apr2015 2 2   May2015 1 1   Jun2015 1 1 2015   4 4   ... 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; Output Year Month Dept1N Dept2N 2014 1Jun2014 2500 2100 2014 1Jul2014 2330 2220 2014 1Aug2014 1500 2140 2014 1Sep2014 2500 2670 2014 1Oct2014 4500 4100 2014 1Nov2014 2600 7100 I need to add a 3 month Avg column based on the following Dept1N(sum of the 1st 3 rows, then 2nd 3 rows...etc.  Example 2500+2330+1500=6330) Dept2N(2100+2220+2140=6460) Add Dept1N+Dept2N (6330+6460=12790 To get my 3 month avg 6460/12790 = 50.50 Can this be best handles in proc report or here in the datastep and how?? Thus the calculation needs to evaluate every 3 months ... View more

data test; infile datalines; input Year $ Dept1N Dept2N ; return; datalines; 2014 2500 2100 2014 200 2100 2014 4400 2100 2015 7700 4130 2015 3320 4110 2015 3660 4150 ; run; data test2; set test; DeptN=Dept1N+Dept2N; DeptPct=Dept1N/DeptN*100; run;   proc report =test2; column Year Dept1N Dept2N DeptN DeptPct; define Year /group; define Dept1N /group; compute after Year; endcomp; run;   I get this Year Dept1N Dept2N DeptN DeptPct 2014 200 2100 2300 8.6956522   2500 2100 4600 54.347826   4400 2100 6500 67.692308 2015 3320 4110 7430 44.683715   3660 4150 7810 46.862996   7700 4130 11830 65.088757   However I want this output, summarized by group Year Dept1N Dept2N DeptN DeptPct           2014 2500 2100 4600 54.34783           2014 200 2100 2300 8.695652           2014 4400 2100 6500 67.69231           2014 7100 6300 13400 43.5786 Group totals each year and average for DeptPct 2015 7700 4130 11830 65.08876           2015 3320 4110 7430 44.68371           2015 3660 4150 7810 46.863           2014 14680 12390 27070 52.21182 Group totals each year and average for DeptPct   Since I already performed the calculations in my datastep I would think proc report is the way to go.  I eventually want to add to an ODS tagset format   ... View more