Author: myyorku2002

VALIDVARNAM and VALIDMENNAME options

I have been using SAS for number of years, so I thought I have encountered enough cases for problem solving to cover the routine analysis work. This time I was trying to read some large SAS files and the SAS9.4 wouldn’t take it. I add the folder to the SAS library, but the SAS file in that folder doesn’t show up. Then I realized the SAS file name contains spaces and starts with numbers which violate the rules for SAS name. One of my colleague mentioned because he can view and open the SAS file through SAS enterprise guide, he think the file is fine. So I was wondering why SAS9.4 can’t open/view the SAS file but SAS enterprise guide can. It turns out SAS enterprise guide runs the options statements at the backend so it can take noncompatible filename and variable name. 

OPTIONS VALIDVARNAME = ANY;
/* This option allows SAS9.4 to read variable names that contains space.  The default is V7.  */
/* V7 rules for SAS variable name:
- up to 32 char
- first char must begin with a letter or the underscore
- trailing blanks are ignored
- cannot contain blanks or special characters */ 

OPTIONS VALIDMEMNAME = EXTEND;
/* This allows SAS9.4 to view data set with name contains blanks and some special characters.  The default is COMPATIBLE. */

Once you run the options of VALIDVARNAME and VALIDMENNAME as shown above in SAS9.4. The SAS dataset will show up in the explorer window and you can click to see the column information. You still need to change the SAS dataset name for the data step to run properly.

Data: REST API

API: Application Program Interface

A. Operation

  • GET: Retrieve
  • POST: Create
  • PUT: Update
  • DELETE: Remove

B. Formats

  • HTML
  • XML
  • Plain text

C. Use

  • Social: Twitter, Facebook
  • Utilities: Dropbox, Google Maps
  • Commerce: Stripe, Mailchimp

D. Other services

  • HATEOAS: Hypermedia As The Engine Of Application State
  • Postman: API testing
  • Spring Data REST

E. Application

  • Python: request

SAS: Multiple Set statements to combine and summerize datasets

A. Code

  • Gather data
%let var = field1 field2 field3 field4 field5;
%let yr = 17;
%let nextyr = 18;
data data&yr.;
length group $1.; 
set Jun&yr. (keep=&var.)
jul&yr. (keep=&var.)
nov&yr. (keep=&var.)
feb&nextyr. (keep=&var.)
mar&nextyr. (keep=&var.);
where field1= "YES";
group = substr(field2, 1, 1);
count = field3;
run;
  • Summarize data
%macro sum (varname, cntvar, group, where, out);
%put &varname.;
%put &cntvar.;
%put &where.;
proc summary nway missing data =data&yr. (where= (field4=&where.));
class field1 &group.;
var &cntvar.;
output out=&out. (drop= field1 _freq_ _type_ ) sum (&cntvar.)=&varname.;
run;
%mend sum;
%sum ( var6, count, group, 'CAT1' , t1);
%sum ( var6, count, , 'CAT1' , sum1);
%sum ( var7, count, group, 'CAT2' , t2);
%sum ( var7, count, , 'CAT2' , sum2);
%sum ( var10, count, group, 'CAT1' and field5 = 'NOV' , t3);
%sum ( var10, count, , 'CAT1' and field5 = 'NOV', sum3);
%sum ( var11, count, group, 'CAT2' and field5 = 'NOV' , t4);
%sum ( var11, count, , 'CAT2' and field = 'NOV' , sum4);
  • Combine data
data data_new;
set t1 sum1 ;
set t2 sum2;
set t3 sum3;
set t4 sum4;
if group = '' then group = 'X';
run;

B. Output

T1 output
Sum1 output
combining T1, T2, T3, T4, Sum1, Sum2, Sum3, Sum4 by group alignment

PowerBI: App Access and Manage Roles

A. Manage roles – in PowerBI desktop

  • on the report, there is a “Language Course Subject Selection” Box
  • need to set up roles so user can access report information only for certain language.
  • under the “Modeling” tab, click “Manage roles”
  • In the “Manage roles” dialog box, click “Create” button to create new roles.
  • I have created 3 roles: “ESL”, “German”, and “Universal Access”.
  • Tables section listed the all tables in the powerBI file. I have two tables in this file: data1 and lang_dimension1. data1 is the student level demographic fact data and lang_dimenstion1 is the language course information.
  • “ESL” role will filter the results for language course description for only “English as a Second Language” courses.
  • “German” role will filter the results for only German language course.
  • “Universal Access” role has no filter.
  • The “Table filter DAX expression” section can be used to define the filter(s)
  • If the filter is applied to a particular role, the filter sign will appear next to the … (more) sign of the table .
  • Click … to access more options of the roles or tables.
The examples shows configuration of the “German” role with the DAX expression on the “lang_dimension1” table subjdesc field.

B. View as Roles – in PowerBI desktop

  • under the “Modeling” tab, click “View as”
  • check “German” and OK
View as “German” role
  • The visual will only show the results for German language courses and “Language Course Subject Selection” will only have “German” listed.
  • There is also a yellow warning message appeared on the top on the visual stating “Now viewing report as: German” and click the “Stop viewing” to stop viewing the report as German role.
  • after setting up the roles and viewing the roles to make sure the roles are functional, publishing the report to the PowerBI workspace.

C. App Access – in PowerBI service

  • Be careful with creating and assigning roles to the report. As soon as you create a role in report, all the end users of the report need to be assign to a role. If no role is assigned, even you add the end users to the app, they will not able to view the powerbi visuals, and all the visuals become blank boxes with a message that you don’t have permission to the underlying dataset.
  • In the PowerBI workspace where the PowerBI report was published, go to the “Datasets” tab and click … of the report and select “Security”
  • select row-level security and add the emails of the member and save.
  • update app.

Git: GitHub Desktop

A. Download

B. Steps

  • set up username and password in github.com
  • in github desktop, create a new repository
Provide name of the repository and local path.
  • Commit files to branch
Make the initial commit. The commit is in the master branch.
  • Publish repository to GitHub
Undo if there is anything wrong.
  • Review the commit in History tab
  • ignore files setup
    • in the same directory of the files being committed, set up a .gitignore text file.
    • *.* and !*.sas : ignore all files in the directory except for .sas files and files in the subfolders.
    • /foldername: ignore subfolders

PowerBI: Analysis of MS Human Resources sample PBIX

A. Sample Dowload: https://docs.microsoft.com/en-us/power-bi/sample-human-resources

B. Report Layout (pages)

  • Info
    • Text Box
    • Image
  • New Hires
    • 4 Text Boxes (page Header, chart footer, additional instructions)
    • New Hires and Actives by Region and Ethnicity (line and Stacked column chart, Employee[actives] is a calculated field)
  • Actives and Separations
  • Bad Hires
  • New Hires Scorecard

C. Tables

D. Measures

  • Employee[Actives]= CALCULATE([EmpCount], FILTER(Employee, ISBLANK(Employee[TermDate])))
  • Employee[actives SPLY]= CALCULATE([Actives],SAMEPERIODLASTYEAR(‘Date'[Date]))
  • Employee[Active YoY % change = Divide([Actives YoY Var], [Actives SPLY])
  • Employee[Actives YoY var]= [Actives]-[Actives SPLY]

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.