Author: myyorku2002

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.

    Statistics: Meta-analysis. 1) Install dmetar package

    A. R code

    install.packages("tidyverse") 
install.packages("meta") 
install.packages("metafor")
devtools::install_github("MathiasHarrer/dmetar") 
# if not working, clone the package from github and unzip and install from local
devtools::install("C:/dmetar-master/dmetar-master")

    B. Error

    Error: (converted from warning) cannot remove prior installation of package ‘digest’

    C. Workaround

    • get library location: Sys.getenv(“R_LIBS_USER”)
    • close R program completely
    • Go to R library: C:\Program Files\R\R-3.6.1\library
    • Delete “digest” folder manually
    • Rerun R with above code and the error message will not appear again and the dmetar package will be successfully intstalled.

    Data: Systematic Review Protocol

    A. Get published Review Protocols from Campbell Collaboration.

    On campbellcollaboration.org webpage, click “Campbell systematic Reviews journal”
    The link takes you to the Wiley Online Library and you will need a library account to browse the contents. Click “Campbell Article Types”
    Select “Protocol” from the list of types.
    Total 248 results which can be further narrowed by selecting “Campbell subject Categories”.

    B. Get published review protocols from Cochrane Reviews.

    https://www.cochranelibrary.com/
    Search ‘mathematical’ in the Title Abstract Keyword and get 1 result under the Cochrance Protocols tab.

    C. Layout of the Protocol

    • Background
      • The problem, condition or issue
      • Intervention
      • How the intervention might work
      • Why it is important to do the review
      • Products of this systematic review
    • Objectives
    • Methodology
      • Criteria for including and excluding studies
        • Types of study designs
        • Time and language
        • Types of participants
        • Types of interventions
        • Duration of follow-up
        • Types of settings
      • Search strategy
      • Search terms
      • Description of methods used in primary research
      • Criteria for determination of independent findings
      • Details of study coding categories
      • Statistical procedures and conventions
      • Studies with multiple groups
      • Unit of analysis issues
      • investigation of heterogeneity
      • Sensitivity analysis
      • missing data and author queries
      • Treatment of qualitative research
    • Reference
    • Review Authors
    • Roles and responsibilities
    • Funding
    • Potential conflict of interest
    • Preliminary timeframe
    • Author declaration

    Statistics: Matrix Calculation in Excel

    A. Matrix Functions in Excel

    • MMULT(): returns the matrix product of 2 arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2. Matrix A multiply Matrix B doesn’t equal to Matrix B multiply Matrix A. The order matters. The multiplication can be done as long as the number of columns of the first matrix equals to the number of rows in the second matrix. To make matrix smaller is to multiply a wider matrix to a long matrix, which returns the matrix with the shorter side of both matrices; and to make a matrix big is to multiple a longer matrix to a wider matrix, which returns the matrix with the longer side of both matrices.
    • MUNIT(): returns the identity matrix for the specified dimension.
    • MINVERSE(): returns the inverse matrix of a given matrix. The product of a matrix and its inverse matrix is the identity matrix. The inverse of a matrix will exist only if the determinant is not zero. The inverse of 2×2 [a, b; c, d] matrix can be calculated as:
      • Calculate the determinant;
      • take the inverse of the determinant;
      • multiple the inverse of the determinant to the [d, -b; -c, a] matrix.
    • MDETERM(): returns the determinant of the square matrix. If matrix determinant is 0, then matrix is sigular. For a 2×2 matrix, the determinant is equal to (ad-bc).

    B. Excel Examples

    • Define matrix
    Define X matrix
    Define Y matrix
    • Transpose Matrix (X’)
    Transpose X to X’ using array formula
    • Matrix multiplication
    Multiplication of X’ and X
    Multiplication of X’ and Y
    • Inverse Matrix (RULE: multiplication of a matrix and its inverse matrix equals to the identity matrix)
    Inverse matrix of X’X
    • Combination of Matrices Operations
    (X’X)^(-10)X’Y

    C. Understand the matrix operation in solving regression equations

    • The matrix multiplication of X’X is to take the sum of product for each combination of all the Xs.
    • The inverse of X’X is converting the X’X into weights for Xs.
    • The matrix multiplication of X’Y is to take the sum of product for each combination of X and Y. Visually, it is like expand the space of X matrix to Y scale.
    • The (X’X)^(-1)X’Y is the estimation of the intercept and coefficient(s).

    D. Reference