%let CPI2017 = 110; %let Y = 2020; Data _null_; %put CPI in 2017 is &CPI2017; %put CPI in %eval(&Y-3) is &CPI%eval(&Y-3); Why does the second put statement not produce the same result as the first one (I get a warning)? I thought maybe I need double ampersand, like this. %put CPI in %eval(&Y-3) is &&CPI%eval(&Y-3); But that also doesn't work, I still only get "CPI in 2017 is &CPI2017". What I want is "CPI in 2017 is 110", just like the first statement. ... View more

I would like to add blank rows of cells in this report. This is what I have so far using a dummy variable and compute block to insert them. Have:   data Cars_report; set sashelp.cars; Total_Count = 1; Blankrow = 1; run; ods excel file="\mydirectory\Cars_report.xlsx"; PROC REPORT DATA = Cars_report nowd; Title "Cars Report mean MSRP"; COLUMN Origin Total_Count DriveTrain MSRP,(Median Mean STD) Blankrow ; DEFINE Origin / group; DEFINE DriveTrain / group; DEFINE Blankrow / group ' ' noprint; BREAK AFTER Origin / summarize style=[background=graydd]; COMPUTE AFTER Origin; DriveTrain="Any"; ENDCOMP; RBREAK AFTER / summarize style=[background=lightblue]; COMPUTE AFTER ; Origin="Any"; DriveTrain="Any"; ENDCOMP; compute after Blankrow; line ' '; line ' '; endcomp; run; ods excel close;   However, there should be 2 blank rows of cells (not one row that is two cells thick). Also, the blank rows should appear after the break summarize lines. Want (with or without the inside cell borders, either of these would be fine):   ... View more

Here are 3 tables from sashelp.cars dataset. I would like to combine them into one excel report. proc report data=sashelp.cars(obs=3) out=Porsche; title "Porsche"; where make = 'Porsche'; column Make Model Type Origin DriveTrain MSRP; proc report data=sashelp.cars(obs=3) out=BMW; title "BMW"; where make = 'BMW'; column Make Model Type Origin DriveTrain MSRP; proc report data=sashelp.cars(obs=3) out=Audi; title "Audi"; where make = 'Audi'; column Make Model Type Origin DriveTrain MSRP; run; However, I don't understand why the titles are all named as the last table 'Audi' when combined like this ? ods excel file="Stacked.xlsx" options(sheet_interval='none' embedded_titles='yes'); proc report data=Porsche; proc report data=BMW; proc report data=Audi; run; ods excel close; What I want is this, each table retaining it's proper title:     ... View more

I am using the SAShelp cars data. Here I am creating a report of the mean MSRP subset according to Origin and DriveTrain BY whether the cars have horsepower greater than 200 or less than equal to 200.   The following gives me almost what I want, but I would like it to appear together in one table rather than two separate tables.   DATA HP_Cars; SET sashelp.cars; IF Horsepower > 200 THEN HP200 = 1; ELSE HP200 = 0; RUN; PROC SORT DATA=Hp_cars; BY HP200; RUN; ODS PDF; PROC REPORT DATA = HP_Cars OUT=Cars_report; Title "Cars Report mean MSRP"; BY HP200; COLUMN Origin DriveTrain MSRP = mean_MSRP ; DEFINE Origin / group; DEFINE DriveTrain / group; DEFINE mean_MSRP / analysis mean "mean_MSRP"; BREAK AFTER Origin / summarize style=[background=graydd]; COMPUTE AFTER Origin; DriveTrain='Any'; ENDCOMP; RBREAK AFTER / summarize style=[background=lightblue]; COMPUTE AFTER; Origin='Any'; DriveTrain='Any'; ENDCOMP; RUN; ODS PDF CLOSE;     Alternatively to the above, is it possible to subset (i.e. using WHERE or IF THEN) within a compute block to calculate the means? The following doesn't actually work, but for the purpose of learning, I would like to know if it's even possible to do something like this: ODS PDF; PROC REPORT DATA = HP_Cars OUT=Cars_report; Title "Cars Report mean MSRP"; COLUMN Origin DriveTrain MSRP = mean_MSRP HP200 mean_MSRP_HP200plus mean_MSRP_HP200less ; DEFINE Origin / group; DEFINE DriveTrain / group; DEFINE mean_MSRP / analysis mean noprint; DEFINE HP200 / computed noprint; DEFINE mean_MSRP_HP200plus / computed "mean_MSRP HP>200"; DEFINE mean_MSRP_HP200less / computed "mean_MSRP HP<=200"; COMPUTE mean_MSRP_HP200plus; IF HP200=1 THEN mean_MSRP_HP200plus = MEAN(MSRP); *calculate mean MSRP for HP > 200; ENDCOMP; COMPUTE mean_MSRP_HP200less; IF HP200=0 THEN mean_MSRP_HP200less = MEAN(MSRP); *calculate mean MSRP for HP <= 200; ENDCOMP; BREAK AFTER Origin / summarize style=[background=graydd]; COMPUTE AFTER Origin; DriveTrain='Any'; ENDCOMP; RBREAK AFTER / summarize style=[background=lightblue]; COMPUTE AFTER; Origin='Any'; DriveTrain='Any'; ENDCOMP; RUN; ODS PDF CLOSE; What I want is this:         ... View more

I would like a macro variable that is referenced to change -1 and +1 with each iteration. For example in the following loop, each iteration from the start year of 2011 to the end year of 2013 should give the year minus 1 (YM1), the year (Y), and the year plus 1 (YP1).     %macro Loop_year(startyear=, endyear=); %DO I = &startyear %TO &endyear; %put YM1 is &I-1; %put Y is &I; %put YP1 is &I+1; %end; %mend Loop_year; %Loop_year(startyear=2011, endyear=2013); This is what I get:   YM1 is 2011-1 Y is 2011 YP1 is 2011+1 YM1 is 2012-1 Y is 2012 YP1 is 2012+1 YM1 is 2013-1 Y is 2013 YP1 is 2013+1   But this is what I want: YM1 is 2010 Y is 2011 YP1 is 2012 YM1 is 2011 Y is 2012 YP1 is 2013 YM1 is 2012 Y is 2013 YP1 is 2014   Would this be possible? Thanks in advance ... View more