Wednesday, 10 September 2025

285.DATA ANALYSIS AND VISUALIZATION OF TEAM SIZE, BUDGET, PROJECT TYPE, TOOLS AND REMOTE POLICY ACROSS GLOBAL SOFTWARE OFFICES SOFTWARE OFFICE REQUIREMENTS WORLDWIDE | PROC SQL | PROC MEANS | PROC FREQ | PROC REPORT | PROC TABULATE | PROC SGPLOT | MACROS

DATA ANALYSIS AND VISUALIZATION OF TEAM SIZE, BUDGET, PROJECT TYPE, TOOLS AND REMOTE POLICY ACROSS GLOBAL  SOFTWARE OFFICES SOFTWARE OFFICE REQUIREMENTS WORLDWIDE | PROC SQL | PROC MEANS | PROC FREQ | PROC REPORT | PROC TABULATE | PROC SGPLOT | MACROS

/*CREATING A DATASET OF SOFTWARE OFFICE REQUIREMENTS*/

1) Create dataset

  options nocenter;

  data work.soft_office_req;

  infile datalines dsd truncover;

  length OfficeName $40 Country $30 Location $30 ProjectType $20 SoftwareTools $80 

         RemotePolicy $10;

  input OfficeID OfficeName $ Country $ Location $ TeamSize ProjectType $ SoftwareTools $

        BudgetUSD RemotePolicy $;

  datalines;

1,"SiliconCore Labs","United States","San Francisco",120,Product,"Java, Python, AWS, Jira",4200000,Hybrid

2,"NileSoft","Egypt","Cairo",45,Platform,"Node.js, MySQL, Docker",900000,Onsite

3,"Bengaluru Pixels","India","Bengaluru",210,Product,"Java, React, Oracle, Jenkins",1800000,Hybrid

4,"Nordic Codeworks","Sweden","Stockholm",60,Infra,"Go, Kubernetes, Prometheus",1200000,Remote

5,"Andes DevHub","Chile","Santiago",30,App,"Flutter, Firebase, Figma",450000,Onsite

6,"Kyoto Systems","Japan","Kyoto",80,Product,"C++, Python, GitLab",1350000,Hybrid

7,"Praha Solutions","Czech Republic","Prague",55,Platform,"Java, Spring, PostgreSQL",750000,Remote

8,"Lagos Binary","Nigeria","Lagos",25,App,"PHP, Laravel, MySQL",300000,Onsite

9,"Melbourne Cloud","Australia","Melbourne",95,Infra,"AWS, Terraform, Ansible",1600000,Hybrid

10,"Toronto DevOps","Canada","Toronto",70,Infra,"Python, Docker, AWS",1100000,Hybrid

11,"Seoul Innovate","South Korea","Seoul",140,Product,"Kotlin, Java, Android Studio",2500000,Hybrid

12,"Berlin Agile","Germany","Berlin",85,Product,"JavaScript, Node.js, AWS",1450000,Remote

13,"Lisbon Labs","Portugal","Lisbon",40,Platform,"Ruby, Rails, PostgreSQL",600000,Hybrid

14,"Mumbai Matrix","India","Mumbai",160,Product,"SAS, R, Python, Git",2000000,Onsite

;

run;

proc print data=work.soft_office_req label noobs;

run;

Output:

OfficeNameCountryLocationProjectTypeSoftwareToolsRemotePolicyOfficeIDTeamSizeBudgetUSD
SiliconCore LabsUnited StatesSan FranciscoProductJava, Python, AWS, JiraHybrid11204200000
NileSoftEgyptCairoPlatformNode.js, MySQL, DockerOnsite245900000
Bengaluru PixelsIndiaBengaluruProductJava, React, Oracle, JenkinsHybrid32101800000
Nordic CodeworksSwedenStockholmInfraGo, Kubernetes, PrometheusRemote4601200000
Andes DevHubChileSantiagoAppFlutter, Firebase, FigmaOnsite530450000
Kyoto SystemsJapanKyotoProductC++, Python, GitLabHybrid6801350000
Praha SolutionsCzech RepublicPraguePlatformJava, Spring, PostgreSQLRemote755750000
Lagos BinaryNigeriaLagosAppPHP, Laravel, MySQLOnsite825300000
Melbourne CloudAustraliaMelbourneInfraAWS, Terraform, AnsibleHybrid9951600000
Toronto DevOpsCanadaTorontoInfraPython, Docker, AWSHybrid10701100000
Seoul InnovateSouth KoreaSeoulProductKotlin, Java, Android StudioHybrid111402500000
Berlin AgileGermanyBerlinProductJavaScript, Node.js, AWSRemote12851450000
Lisbon LabsPortugalLisbonPlatformRuby, Rails, PostgreSQLHybrid1340600000
Mumbai MatrixIndiaMumbaiProductSAS, R, Python, GitOnsite141602000000


Purpose: Sort dataset by Country and TeamSize desc for reporting 

proc sort data=work.soft_office_req out=work.soft_office_sorted;

  by Country descending TeamSize;

run;

proc print data=work.soft_office_sorted(obs=7) label noobs;

run;

Output:

OfficeNameCountryLocationProjectTypeSoftwareToolsRemotePolicyOfficeIDTeamSizeBudgetUSD
Melbourne CloudAustraliaMelbourneInfraAWS, Terraform, AnsibleHybrid9951600000
Toronto DevOpsCanadaTorontoInfraPython, Docker, AWSHybrid10701100000
Andes DevHubChileSantiagoAppFlutter, Firebase, FigmaOnsite530450000
Praha SolutionsCzech RepublicPraguePlatformJava, Spring, PostgreSQLRemote755750000
NileSoftEgyptCairoPlatformNode.js, MySQL, DockerOnsite245900000
Berlin AgileGermanyBerlinProductJavaScript, Node.js, AWSRemote12851450000
Bengaluru PixelsIndiaBengaluruProductJava, React, Oracle, JenkinsHybrid32101800000


2) MACRO SECTION

Purpose: Provide reusable code pieces for filtering and summarizing offices

Purpose: Macro %filter_by_country - creates a filtered dataset for a given country 

%macro filter_by_country(country=);

  %let dsname = soft_office_%sysfunc(compress(&country,%str( )));

  proc sql;

    create table work.&dsname as

    select * 

    from work.soft_office_req

    where upcase(country)=upcase("&country");

  quit;

  /*Use ATTRN for numeric attribute NOBS */

  %let nobs=%sysfunc(attrn(%sysfunc(open(work.&dsname)), nobs));

  %put NOTE: Created &dsname with &nobs observations.;

  proc print data=work.&dsname; 

  run;

%mend filter_by_country;


%filter_by_country(country=India);

Log:

NOTE: Created soft_office_India with 2 observations.

Output:

ObsOfficeNameCountryLocationProjectTypeSoftwareToolsRemotePolicyOfficeIDTeamSizeBudgetUSD
1Bengaluru PixelsIndiaBengaluruProductJava, React, Oracle, JenkinsHybrid32101800000
2Mumbai MatrixIndiaMumbaiProductSAS, R, Python, GitOnsite141602000000

Purpose: Macro %summarize_budget - produce aggregate budget stats by ProjectType 

%macro summarize_budget(in_ds=work.soft_office_req, out_ds=work.budget_summary);

  proc sql;

    create table &out_ds as

    select ProjectType,

           count(*) as Offices,

           sum(BudgetUSD) as TotalBudget format=dollar12.,

           mean(BudgetUSD) as AvgBudget format=dollar12.,

           median(BudgetUSD) as MedianBudget format=dollar12.

    from &in_ds

    group by ProjectType;

  quit;

  proc print;run;

%mend summarize_budget;


%summarize_budget(in_ds=work.soft_office_req, out_ds=work.budget_summary);

Output:

ObsProjectTypeOfficesTotalBudgetAvgBudgetMedianBudget
1App2$750,000$375,000$375,000
2Infra3$3,900,000$1,300,000$1,200,000
3Platform3$2,250,000$750,000$750,000
4Product6$13,300,000$2,216,667$1,900,000


Purpose: Macro %flag_large_teams - create a flag for large teams based on threshold

%macro flag_large_teams(ds=work.soft_office_req, threshold=100);

  data work.flagged;

    set &ds;

    if TeamSize >= &threshold then LARGE_TEAM='Y'; else LARGE_TEAM='N';

  run;

  proc print;run;

%mend flag_large_teams;


%flag_large_teams(ds=work.soft_office_req, threshold=100);

Output:

ObsOfficeNameCountryLocationProjectTypeSoftwareToolsRemotePolicyOfficeIDTeamSizeBudgetUSDLARGE_TEAM
1SiliconCore LabsUnited StatesSan FranciscoProductJava, Python, AWS, JiraHybrid11204200000Y
2NileSoftEgyptCairoPlatformNode.js, MySQL, DockerOnsite245900000N
3Bengaluru PixelsIndiaBengaluruProductJava, React, Oracle, JenkinsHybrid32101800000Y
4Nordic CodeworksSwedenStockholmInfraGo, Kubernetes, PrometheusRemote4601200000N
5Andes DevHubChileSantiagoAppFlutter, Firebase, FigmaOnsite530450000N
6Kyoto SystemsJapanKyotoProductC++, Python, GitLabHybrid6801350000N
7Praha SolutionsCzech RepublicPraguePlatformJava, Spring, PostgreSQLRemote755750000N
8Lagos BinaryNigeriaLagosAppPHP, Laravel, MySQLOnsite825300000N
9Melbourne CloudAustraliaMelbourneInfraAWS, Terraform, AnsibleHybrid9951600000N
10Toronto DevOpsCanadaTorontoInfraPython, Docker, AWSHybrid10701100000N
11Seoul InnovateSouth KoreaSeoulProductKotlin, Java, Android StudioHybrid111402500000Y
12Berlin AgileGermanyBerlinProductJavaScript, Node.js, AWSRemote12851450000N
13Lisbon LabsPortugalLisbonPlatformRuby, Rails, PostgreSQLHybrid1340600000N
14Mumbai MatrixIndiaMumbaiProductSAS, R, Python, GitOnsite141602000000Y


3) PROC EXAMPLES

Purpose: PROC FREQ - show distribution of ProjectType and RemotePolicy 

proc freq data=work.soft_office_req nlevels; 

  tables ProjectType RemotePolicy / nocum norow nopercent; 

run;

Output:

The FREQ Procedure

Number of Variable Levels
VariableLevels
ProjectType4
RemotePolicy3
ProjectTypeFrequency
App2
Infra3
Platform3
Product6
RemotePolicyFrequency
Hybrid7
Onsite4
Remote3

Purpose: PROC MEANS - provide summary statistics for TeamSize and BudgetUSD 

proc means data=work.soft_office_req n mean median std min max maxdec=0;

  var TeamSize BudgetUSD;

run;

Output:

The MEANS Procedure

VariableNMeanMedianStd DevMinimumMaximum
TeamSize
BudgetUSD
14
14
87
1442857
75
1275000
54
1005534
25
300000
210
4200000

Purpose: PROC TABULATE - create a cross-tab of RemotePolicy by ProjectType with budgets 

proc tabulate data=work.soft_office_req missing;

  class ProjectType RemotePolicy;

  var BudgetUSD TeamSize;

  table ProjectType,

        RemotePolicy all,

        (BudgetUSD*(sum mean) TeamSize*(mean));

run;

Output:

ProjectType App

 BudgetUSDTeamSize
SumMeanMean
RemotePolicy750000.00375000.0027.50
Onsite
All750000.00375000.0027.50

ProjectType Infra

 BudgetUSDTeamSize
SumMeanMean
RemotePolicy2700000.001350000.0082.50
Hybrid
Remote1200000.001200000.0060.00
All3900000.001300000.0075.00

ProjectType Platform

 BudgetUSDTeamSize
SumMeanMean
RemotePolicy600000.00600000.0040.00
Hybrid
Onsite900000.00900000.0045.00
Remote750000.00750000.0055.00
All2250000.00750000.0046.67

ProjectType Product

 BudgetUSDTeamSize
SumMeanMean
RemotePolicy9850000.002462500.00137.50
Hybrid
Onsite2000000.002000000.00160.00
Remote1450000.001450000.0085.00
All13300000.002216666.67132.50

Purpose: PROC REPORT - produce a formatted report with derived columns 

proc report data=work.flagged nowd;

  column OfficeID OfficeName Country Location TeamSize ProjectType 

         LARGE_TEAM BudgetUSD RemotePolicy;

  define BudgetUSD / analysis sum format=dollar12.;

  compute before _page_ ;

    line '--- SOFTWARE OFFICE REQUIREMENTS REPORT ---';

  endcomp;

run;

Output:

--- SOFTWARE OFFICE REQUIREMENTS REPORT ---
OfficeIDOfficeNameCountryLocationTeamSizeProjectTypeLARGE_TEAMBudgetUSDRemotePolicy
1SiliconCore LabsUnited StatesSan Francisco120ProductY$4,200,000Hybrid
2NileSoftEgyptCairo45PlatformN$900,000Onsite
3Bengaluru PixelsIndiaBengaluru210ProductY$1,800,000Hybrid
4Nordic CodeworksSwedenStockholm60InfraN$1,200,000Remote
5Andes DevHubChileSantiago30AppN$450,000Onsite
6Kyoto SystemsJapanKyoto80ProductN$1,350,000Hybrid
7Praha SolutionsCzech RepublicPrague55PlatformN$750,000Remote
8Lagos BinaryNigeriaLagos25AppN$300,000Onsite
9Melbourne CloudAustraliaMelbourne95InfraN$1,600,000Hybrid
10Toronto DevOpsCanadaToronto70InfraN$1,100,000Hybrid
11Seoul InnovateSouth KoreaSeoul140ProductY$2,500,000Hybrid
12Berlin AgileGermanyBerlin85ProductN$1,450,000Remote
13Lisbon LabsPortugalLisbon40PlatformN$600,000Hybrid
14Mumbai MatrixIndiaMumbai160ProductY$2,000,000Onsite

data work.tool_long;

  set work.soft_office_req;

  length Tool $30;

  i=1;

  do until(scan(softwaretools,i,' ,')='');

    Tool = strip(scan(softwaretools,i,','));

    if Tool ne '' then output;

    i+1;

  end;

  keep OfficeID OfficeName Tool;

run;

proc print;run;

Output:

ObsOfficeNameOfficeIDTool
1SiliconCore Labs1Java
2SiliconCore Labs1Python
3SiliconCore Labs1AWS
4SiliconCore Labs1Jira
5NileSoft2Node.js
6NileSoft2MySQL
7NileSoft2Docker
8Bengaluru Pixels3Java
9Bengaluru Pixels3React
10Bengaluru Pixels3Oracle
11Bengaluru Pixels3Jenkins
12Nordic Codeworks4Go
13Nordic Codeworks4Kubernetes
14Nordic Codeworks4Prometheus
15Andes DevHub5Flutter
16Andes DevHub5Firebase
17Andes DevHub5Figma
18Kyoto Systems6C++
19Kyoto Systems6Python
20Kyoto Systems6GitLab
21Praha Solutions7Java
22Praha Solutions7Spring
23Praha Solutions7PostgreSQL
24Lagos Binary8PHP
25Lagos Binary8Laravel
26Lagos Binary8MySQL
27Melbourne Cloud9AWS
28Melbourne Cloud9Terraform
29Melbourne Cloud9Ansible
30Toronto DevOps10Python
31Toronto DevOps10Docker
32Toronto DevOps10AWS
33Seoul Innovate11Kotlin
34Seoul Innovate11Java
35Seoul Innovate11Android Studio
36Berlin Agile12JavaScript
37Berlin Agile12Node.js
38Berlin Agile12AWS
39Lisbon Labs13Ruby
40Lisbon Labs13Rails
41Lisbon Labs13PostgreSQL
42Mumbai Matrix14SAS
43Mumbai Matrix14R
44Mumbai Matrix14Python
45Mumbai Matrix14Git


Purpose: PROC SQL - count tool frequency across offices 

proc sql;

  create table work.tool_counts as

  select Tool, count(distinct OfficeID) as OfficesUsing format=8.

  from work.tool_long

  group by Tool

  order by OfficesUsing desc;

quit;

proc print;run;

Output:

ObsToolOfficesUsing
1Python4
2Java4
3AWS4
4Node.js2
5MySQL2
6Docker2
7PostgreSQL2
8React1
9R1
10Jenkins1
11SAS1
12Ansible1
13Figma1
14Prometheus1
15PHP1
16GitLab1
17Oracle1
18Go1
19Git1
20Laravel1
21JavaScript1
22Rails1
23Firebase1
24Ruby1
25Jira1
26Spring1
27Kubernetes1
28Android Studio1
29C++1
30Flutter1
31Kotlin1
32Terraform1


Purpose: PROC SGPLOT - visualize TeamSize distribution

ods graphics on;

proc sgplot data=work.soft_office_req;

  histogram TeamSize / nbins=8;

  density TeamSize;

  xaxis label='Team Size';

  title 'Distribution of Team Sizes Across Offices';

run;

ods graphics off;

Output:



Purpose: PROC SQL join example - combine budget summary with office details

proc sql;

  create table work.enriched as

  select a.*, b.TotalBudget, b.AvgBudget, b.Offices

  from work.soft_office_req a left join work.budget_summary b

  on a.ProjectType = b.ProjectType;

quit;

proc print;run;

Output:

ObsOfficeNameCountryLocationProjectTypeSoftwareToolsRemotePolicyOfficeIDTeamSizeBudgetUSDTotalBudgetAvgBudgetOffices
1Andes DevHubChileSantiagoAppFlutter, Firebase, FigmaOnsite530450000$750,000$375,0002
2Lagos BinaryNigeriaLagosAppPHP, Laravel, MySQLOnsite825300000$750,000$375,0002
3Toronto DevOpsCanadaTorontoInfraPython, Docker, AWSHybrid10701100000$3,900,000$1,300,0003
4Nordic CodeworksSwedenStockholmInfraGo, Kubernetes, PrometheusRemote4601200000$3,900,000$1,300,0003
5Melbourne CloudAustraliaMelbourneInfraAWS, Terraform, AnsibleHybrid9951600000$3,900,000$1,300,0003
6Lisbon LabsPortugalLisbonPlatformRuby, Rails, PostgreSQLHybrid1340600000$2,250,000$750,0003
7Praha SolutionsCzech RepublicPraguePlatformJava, Spring, PostgreSQLRemote755750000$2,250,000$750,0003
8NileSoftEgyptCairoPlatformNode.js, MySQL, DockerOnsite245900000$2,250,000$750,0003
9Mumbai MatrixIndiaMumbaiProductSAS, R, Python, GitOnsite141602000000$13,300,000$2,216,6676
10Bengaluru PixelsIndiaBengaluruProductJava, React, Oracle, JenkinsHybrid32101800000$13,300,000$2,216,6676
11Kyoto SystemsJapanKyotoProductC++, Python, GitLabHybrid6801350000$13,300,000$2,216,6676
12Berlin AgileGermanyBerlinProductJavaScript, Node.js, AWSRemote12851450000$13,300,000$2,216,6676
13SiliconCore LabsUnited StatesSan FranciscoProductJava, Python, AWS, JiraHybrid11204200000$13,300,000$2,216,6676
14Seoul InnovateSouth KoreaSeoulProductKotlin, Java, Android StudioHybrid111402500000$13,300,000$2,216,6676




To Visit My Previous Proc  Means And Nway Option:Click Here
To Visit My Previous Proc Means And CharType Option:Click Here
To Visit My Previous SAS Functions:Click Here
To Visit My Previous Length Statement Using In Many Ways:Click Here



Follow Us On : 


 


--- FOLLOW OUR BLOG FOR MORE INFORMATION.

--->PLEASE DO COMMENTS AND SHARE OUR BLOG.

on

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)