Author: myyorku2002

SAS: Tagset.Excelxp Double loop with multiple sheets output depend on dynamic parameter table

A. Purpose

The use of this SAS program is to produce excel files for each product, and within each excel product file, generate multiple sheets with different conditions, sheets names, titles.

B. Code

  • Set up parameter table
options symbolgen;
data parmlst;
infile datalines delimiter = '!';
length  parm1 $60. parm2 $30. parm3 $40.;
input parm1 $ parm2 $  parm3 $ ;
call symput('parm1_'||left(_n_), parm1);
call symput('parm2_'||left(_n_), parm2);
call symput('parm3_'||left(_n_), parm3);
datalines;
engineSize le 3.5  ! EngineSize le 3.5! (Engine Size = $40,000)
;
run;
  • Set up tagsets excel output macro
/* [S=] is [S=<parent>] */
%macro exlout (varname=, con=);
%let  i=1;
%do %while (%scan(&con., &i, ' ') ne );
%let dsn=%scan(&con., &i, ' ');
data &dsn.;
set sashelp.cars;
if &varname. = "&dsn."; 
count = 1;
run;
ods tagsets.Excelxp path= "X:\SAS\" file="Car_Profile_&varname._&dsn..xls" style=journal
options (embedded_titles='yes' embedded_footnotes='yes'  absolute_column_width='10,10,10,10' auto_height="no" );
%do j=1 %to 2;
%put &j;
ods tagsets.excelxp options (sheet_name="&&parm2_&j." sheet_interval='none');
title1 justify = left  height = 22pt bold "Car Profile (&varname. = &dsn.) by Specifics";
title2 justify = left  height = 20pt bold "Car Segment: &&parm3_&j. ";
proc tabulate data = &dsn.  S=[background=palg];
where &&parm1_&j.; 
class Type Make Origin engineSize Cylinders / S=[background=palg];
classlev Type Make Origin engineSize Cylinders / S=[background=palg];
var Horsepower MPG_City MPG_Highway Count/ S=[background=palg];
table Type all="Total"*[S=]
Make all="Total"*[S=]
Origin all="Total"*[S=]
EngineSize="EngineSize" all="Total"*[S=]
Cylinders all="Total"*[S=],
(Horsepower*Mean="" MPG_City="MPG City"*Mean="" MPG_Highway="MPG City"*Mean="" Count="N"*SUM="" )
/ box = [S=[background=palg]] ;
keyword all / S=[background=lilg];
run;
quit;
%end;
ods tagsets.excelxp close;
%let i = %eval (&i +1);
%end;
ods tagsets.excelxp close;
run;
%mend exlout;
  • run macro
%exlout (varname=Origin, con=USA Europe);
%exlout (varname=Type, con=Sedan SUV);

C. Output

Car_Profile_Origin_Europe.xls

D. Key Coding Elements

  • loop: %let i=1; %do %while (%scan(&con. &i, ‘ ‘) ne );
  • loop: %do j= 1 %to 2;
  • Colouring: S=[background=palg]; palg and lilg are different shades of green.
  • macro variable assignment: call symput (‘parm1_’||left(_n_), parm1);
  • Customization of excel sheets: data parmlst;

SAS: Proc Tabulate Table Colouring

/*Original table*/
proc tabulate data =table1 ;
class Agegroup gender acadyear;
var heads;
table agegroup="Age Group" all gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > =""  );
run;
/* Change body to light green */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear;
var heads;
table agegroup="Age Group" all gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum <agegroup all gender all > =""  );
run;
Add S=[background=lightgreen] in the Proc tabulate statement.
/* Change Class Variable Colour */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
var heads;
table agegroup="Age Group" all gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > ="" );
run;
Add / S=[background=lightgreen] in the Class statement.
/* Change var variable column heading colour */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
var heads / S=[background=lightgreen];
table agegroup="Age Group" all gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > =""  );
run;
Add / S=[background=lightgreen] in the Var statement.
/* Change Box colour */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
var heads / S=[background=lightgreen] ;
table agegroup="Age Group" all  gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > ="" )
/ box =[S=[background=lightgreen]];
run;
Add / / box =[S=[background=lightgreen]] in the Table statement. 
/* Change Class categories colour */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
classlev Agegroup gender acadyear /  S=[background=lightgreen];
var heads / S=[background=lightgreen] ;
table agegroup="Age Group" all  gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > =""  )
/ box =[S=[background=lightgreen]];
run;quit;
Add class variables and / S=[background=lightgreen] to the Classlev statement.
/* Change Colour of 'All' field */ 
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
classlev Agegroup gender acadyear /  S=[background=lightgreen];
var heads / S=[background=lightgreen] ;
table agegroup="Age Group" all  gender="Gender" all ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > =""  )
/ box =[S=[background=lightgreen]];
keyword all / S=[background=green];
run;
Add all / S=[background=green] to the Keyword statement.
/* Rename 'All' to 'Total'. Change 'Total' row colour Across the table. */
/* Method 1 */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
classlev Agegroup gender acadyear /  S=[background=lightgreen];
var heads / S=[background=lightgreen] ;
table agegroup="Age Group" all="Total"*[s=]  gender="Gender" all="Total"*[s= < parent >]  ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > =""  )
/ box =[S=[background=lightgreen]];
keyword all / S=[background=green];
run;
/* Method 2 */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
classlev Agegroup gender acadyear /  S=[background=lightgreen];
var heads / S=[background=lightgreen] ;
table agegroup="Age Group" all="Total"*[S=[background=green]]  gender="Gender" all="Total"*[S=[background=green]]  ,
(acadyear="")*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > =""  )
/ box =[S=[background=lightgreen]];
keyword all / S=[background=green];
run;
Add *[S=[background=green]] to the all labels in the Table statement. 
/* Add All column Total. Override colour of All column */
proc tabulate data =table1 S=[background=lightgreen] ;
class Agegroup gender acadyear / S=[background=lightgreen];
classlev Agegroup gender acadyear /  S=[background=lightgreen];
var heads / S=[background=lightgreen] ;
table agegroup="Age Group" all="Total"*[S=[background=green]]  gender="Gender" all="Total"*[S=[background=green]]  ,
(acadyear="" all={label="All Years" S=[background=lightgreen]} )*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > ="" )
/ box =[S=[background=lightgreen]];
keyword all / S=[background=green];
run;
In Table statement, add all={label=”All Years” S=[background=lightgreen]} to override the colour setup in the Keyword statement.

PowerBI Report Share through Workspace and PowerBI Apps

A. PowerBI Free and Pro versions

  • PowerBI free version doesn’t have the “Embed” and “Embed in SharePoint Online” options under the “File” menu. It can only publish dashboard and reports to the public. When dashboard and reports are published to the publish, the filter menu will not show in the view, so the end users can only use slicers to filter the results or interact with the dashboard/reports through cross filtering of visuals.
  • PowerBI free version cannot create Workspace, therefore not able to package reports in the Workspace into PowerBI app. You need a Pro licence to do that.
  • Only PowerBI Pro licence can view the PowerBI apps, the free version cannot.
File menu options for Free for PowerBI
File menu for Pro version of PowerBI
Pages: Page 1, Page 2, Page 3

SAS: Tagset.Excelxp title and footnote setup

A. Multiple Titles/Footnotes

title
title2
title3
footnote
footnote2
footnote3

B. Alignment: default is center. 

justify=

C. Font

f=

D.Bold/Italic

bold italic

E. Font size

h=

F. Example

title1 f='Calibri' h=14pt 'Report Title';
title2 "Report Title1";
title3 "Report Title2";
footnote "Prepared by First Last on %sysfunc(date(),worddate.)";
footnote2 justify=left h=8pt bold "Note:";
footnote3 justify=left h=8pt italic "footnote3";

SAS: Date Functions and Date Value

A. Extract Month, Year information from DateTime.

  • Convert DateTime to Date using Datepart() first.
  • Use Year() and Month() function to get the Month and Year information
date=datepart(datetime);
year=year(date);
month=month(date);

B. Date Value for Date Comparison

  • ’17OCT1991’D is 11612, and is SAS date Oct. 17, 1991
  • use single quotation and D at the end
  • the day, month, or year in the date string can be replaced by macro variable to become dynamic. eg. ’01JUL&yr.” can be used for comparison of July 1 of different year.

C. Age calculation

  • Reference: https://communities.sas.com/t5/SAS-Enterprise-Guide/How-to-calculate-age-from-Date-of-birth-and-date-of-last-visit/td-p/572236
  • Reference: https://blogs.sas.com/content/iml/2017/05/15/intck-intnx-intervals-sas.html
  • intck function results whole year and yrdif function results decimal year.
  • In intck function, using the ‘continuous’ or ‘c’ option to return the number of full years. 
    age = intck('Year', birth, "01JUL&yr."D, 'C');

    SAS: Standard Errors

    A. Different Standard Errors

    • Standard Error of the Regression Model: (denote by s) usually referred to as the standard error of the regression or “standard error of the estimate”.
    • STDI: Standard Error of the individual predicted value (y_hat)
    • STDP: Standard Error of the mean predicated value
    • STDR:Standard Error of the residual
    • STUDENT: Studentized residuals, the residual divided by its standard error

    B. Estimate function in the GLM procedure

    R: RStudio Plots not showing in the Plots Window

    A. Symptom

    The plots stopped showing in the pane when running the R codes. The temporary png file generated in the folder didn’t have the graphic output and the file was not able to be deleted.

    B. R Code Caused problem

    png(file='Construct.png') 
subgroup.analysis.mixed.effects(x = m.hksj, subgroups = madata$Construct, plot=TRUE)
dev.off

    In R use png(file=) and dev.off to generate graphic output in the working directory. The subgroup.analysis.mixed.effect function doesn’t work with this method, hence the output graphic file got stuck.

    C. Fix

    dev.cur()   # to identify output device as "png 4"
dev.off()  # to identify "null device" so the png file in the folder can be deleted;
getOption("device") # to set option to "RStudioGD"

    R: Update R version

    A. Update R through RGui

    • Open RGui through icon or open RGui from the directory (C:\Program Files\R\R-3.6.1\bin\x64\Rgui.exe)
    • In the Gui, enter the following.
    install.packages("installr")
library(installr)
    • Under “install” menu, click “Update R”.
    • Click through the dialogue boxes.