/*************************************************************************************
  The five SAS macros below enable you to sort the HPOV SAS data set for platform 
  mapping of aggregation metrics according to your specifications. The required
  input SAS data is available on the same site where this code is available.

  The following macros are provided:
   
   EColumns: Columns by platform Environment
   PMetrics: Metric columns by performance area 
   TColumns: Columns by table
   FColumns: Columns by funtion
   AColumns: Columns that are common across all platforms

  Perform the following steps to customize this code.
  1. Copy the HPOV SAS data set to a folder on your SAS server.
  2. Modify the libname statement path to point to the folder where you copied
     the HPOV data set on your SAS server. 
  3. Modify the dataset name used when copying the HPOV data set to your SAS server.
  4. Comment and uncomment the desired macro invocations to execute the desired 
     macros.
*************************************************************************************/

/* Modify the libname statement path. */
libname HpovData 'c:\Users\YourUserId\YourCode';

/* Modify the dataset name used to save your data set. */ 
%let HpovDatasetName = Hpov_Cross_Reference_Data;


/* This macro lists columns that are present in individual platform environments. */
%macro EColumns(Env=);
proc sql;
  create table Work.EColumns as
    select StagedTable, ColumnName, ColumnType, ColumnLabel, PerformanceArea, Notes
	from Hpovdata.&HpovDatasetName
	where &Env = 'X'
	order by StagedTable, ColumnType, ColumnLabel;
quit;
%mend;
/* Comment and uncomment the desired macro calls by adding or removing an asterisk. */
%EColumns(Env=aix);
*%EColumns(Env=hpux);
*%EColumns(Env=sun);
*%EColumns(Env=linux);
*%EColumns(Env=dec);
*%EColumns(Env=windows);


/* This macro lists columns that are present by performance area. */
%macro PMetrics(PerfArea=);
proc sql;
  create table Work.PMetrics as
    select StagedTable, ColumnName, ColumnLabel, aix, hpux, sun, linux, dec, windows, Notes
	from HpovData.&HpovDatasetName
	where PerformanceArea = "&PerfArea" and ColumnType='Metric'
	order by StagedTable, ColumnLabel;
quit;
%mend;
/* Comment and uncomment the desired macro calls by adding or removing an asterisk. */
%PMetrics(PerfArea=System);
*%PMetrics(PerfArea=Cpu);
*%PMetrics(PerfArea=Memory);
*%PMetrics(PerfArea=Paging);
*%PMetrics(PerfArea=Swap);
*%PMetrics(PerfArea=Disk);
*%PMetrics(PerfArea=Queueing);
*%PMetrics(PerfArea=Network);


/* This macro lists all columns for an ITRM defined table. */
%macro TColumns(table=);
proc sql;
  create table Work.TColumns as
    select ColumnName, ColumnLabel, ColumnType, PerformanceArea, aix, hpux, sun, linux, dec, windows, Notes
	from HpovData.&HpovDatasetName
	where StagedTable = "&table" 
	order by ColumnType, PerformanceArea, ColumnLabel;
quit;
%mend;
/* Comment and uncomment the desired macro calls by adding or removing an asterisk. */
%TColumns(Table=PCSGLB);
*%TColumns(Table=PCSCPU);
*%TColumns(Table=PCSDSK);
*%TColumns(Table=PCSNET);
*%TColumns(Table=PCSLS);

/* This macro contains an sql procedures that lists ranked columns, computed columns 
   and normalized columns across platform Environments. */
%macro FColumns;
proc sql;
  create table Work.Ranked as
    select StagedTable, ColumnName, ColumnLabel, PerformanceArea, aix, hpux, sun, linux, dec, windows, Notes
	from HpovData.&HpovDatasetName
	where substr(Notes,1,6) = 'Ranked'
	order by StagedTable, PerformanceArea, ColumnLabel;
  create table Work.Computed as
    select StagedTable, ColumnName, ColumnLabel, PerformanceArea, aix, hpux, sun, linux, dec, windows, Notes
	from HpovData.&HpovDatasetName
	where index(Notes,'Computed') > 0
	order by StagedTable, PerformanceArea, ColumnLabel;
  create table Work.Normalized as
    select StagedTable, ColumnName, ColumnLabel, PerformanceArea, aix, hpux, sun, linux, dec, windows, Notes
	from Hpovdata.&HpovDatasetName
	where index(Notes,'Normalized') > 0
	order by StagedTable, PerformanceArea, ColumnLabel;
quit;
%mend;
/* Comment or uncomment the macro call by adding or removing an asterisk. */
%FColumns;


/* This macro contains an sql procedure that lists columns that are present 
   in all Unix platform Environments. */
%macro AColumns;
proc sql;
  create table Work.AllEnv as
    select StagedTable, ColumnName, ColumnLabel, PerformanceArea, Notes
	from HpovData.&HpovDatasetName
	where aix = 'X' and hpux = 'X' and sun = 'X' and linux = 'X' and dec = 'X' and windows = 'X'  
	order by StagedTable, ColumnLabel;
quit;
%mend;
/* Comment or uncomment the macro call by adding or removing an asterisk. */
%AColumns;