371.Can SAS Predict the Next Infrastructure Crisis? Analyzing Famous Global Cities

Can SAS Predict the Next Infrastructure Crisis? Analyzing Famous Global Cities

HERE IN THIS PROJECT WE USED THESE SAS STATEMENTS --PROC SQL | PROC MEANS | PROC CORR | PROC SGPLOT | MACROS | DATE FUNCTIONS (MDY-INTCK-INTNX) | MERGE | SET | APPEND | TRANSPOSE

1. Why City Infrastructure Analytics Matters

Modern cities are not judged only by population or tourism. They are evaluated by:

• Number of buildings (urbanization)
• Length of roads (connectivity)
• Public transport availability (mobility)
• Internet speed (digital readiness)

Governments, investors, real-estate developers, and smart-city planners analyze these metrics to decide:

• Where to invest
• Where to expand transport
• Which cities are technologically ready

This project simulates how such analytics is performed in SAS using structured city data.

2. Create City Infrastructure Dataset

data cities_raw;

    format Record_Date date9.;

    input City_Name $ Buildings Roads_km Public_Transport Internet_Speed 

          Record_Date :date9.;

    datalines;

NewYork 1200000 14000 9 220 01JAN2024

London 900000 9500 8 210 05JAN2024

Tokyo 1500000 18000 10 240 10JAN2024

Paris 800000 7000 7 190 12JAN2024

Dubai 600000 5000 6 250 15JAN2024

Singapore 500000 4000 9 230 20JAN2024

Sydney 700000 8000 7 200 22JAN2024

Mumbai 1300000 12000 6 160 25JAN2024

Delhi 1100000 11000 6 150 27JAN2024

Berlin 650000 6000 7 195 30JAN2024

Toronto 750000 8500 8 205 01FEB2024

Seoul 1000000 9000 9 225 03FEB2024

HongKong 600000 3500 9 235 05FEB2024

LosAngeles 900000 10000 7 210 07FEB2024

Rome 500000 4500 6 180 10FEB2024

Madrid 550000 5000 7 185 12FEB2024

run;

proc print data=cities_raw;

run;

OUTPUT:

Obs	Record_Date	City_Name	Buildings	Roads_km	Public_Transport	Internet_Speed
1	01JAN2024	NewYork	1200000	14000	9	220
2	05JAN2024	London	900000	9500	8	210
3	10JAN2024	Tokyo	1500000	18000	10	240
4	12JAN2024	Paris	800000	7000	7	190
5	15JAN2024	Dubai	600000	5000	6	250
6	20JAN2024	Singapor	500000	4000	9	230
7	22JAN2024	Sydney	700000	8000	7	200
8	25JAN2024	Mumbai	1300000	12000	6	160
9	27JAN2024	Delhi	1100000	11000	6	150
10	30JAN2024	Berlin	650000	6000	7	195
11	01FEB2024	Toronto	750000	8500	8	205
12	03FEB2024	Seoul	1000000	9000	9	225
13	05FEB2024	HongKong	600000	3500	9	235
14	07FEB2024	LosAngel	900000	10000	7	210
15	10FEB2024	Rome	500000	4500	6	180
16	12FEB2024	Madrid	550000	5000	7	185

3. Date Engineering Using MDY, INTCK, INTNX

data cities_dates;

    set cities_raw;

    Report_Date = mdy(3,1,2024);

    Months_Since = intck('month', Record_Date, Report_Date);

    Next_Review = intnx('month', Record_Date, 3, 'same');

    format Report_Date Next_Review date9.;

run;

proc print data=cities_dates;

run;

OUTPUT:

Obs	Record_Date	City_Name	Buildings	Roads_km	Public_Transport	Internet_Speed	Report_Date	Months_Since	Next_Review
1	01JAN2024	NewYork	1200000	14000	9	220	01MAR2024	2	01APR2024
2	05JAN2024	London	900000	9500	8	210	01MAR2024	2	05APR2024
3	10JAN2024	Tokyo	1500000	18000	10	240	01MAR2024	2	10APR2024
4	12JAN2024	Paris	800000	7000	7	190	01MAR2024	2	12APR2024
5	15JAN2024	Dubai	600000	5000	6	250	01MAR2024	2	15APR2024
6	20JAN2024	Singapor	500000	4000	9	230	01MAR2024	2	20APR2024
7	22JAN2024	Sydney	700000	8000	7	200	01MAR2024	2	22APR2024
8	25JAN2024	Mumbai	1300000	12000	6	160	01MAR2024	2	25APR2024
9	27JAN2024	Delhi	1100000	11000	6	150	01MAR2024	2	27APR2024
10	30JAN2024	Berlin	650000	6000	7	195	01MAR2024	2	30APR2024
11	01FEB2024	Toronto	750000	8500	8	205	01MAR2024	1	01MAY2024
12	03FEB2024	Seoul	1000000	9000	9	225	01MAR2024	1	03MAY2024
13	05FEB2024	HongKong	600000	3500	9	235	01MAR2024	1	05MAY2024
14	07FEB2024	LosAngel	900000	10000	7	210	01MAR2024	1	07MAY2024
15	10FEB2024	Rome	500000	4500	6	180	01MAR2024	1	10MAY2024
16	12FEB2024	Madrid	550000	5000	7	185	01MAR2024	1	12MAY2024

Business meaning:

·       Report_Date → Corporate reporting date

·       Months_Since → How old the data is

·       Next_Review → When city data will be reviewed again

4. Infrastructure Score Macro

%macro infra_score;

data cities_score;

    set cities_dates;

    Infrastructure_Score = (Buildings/100000) + (Roads_km/1000) + (Public_Transport*5)

                           +  (Internet_Speed/10);

run;

proc print data=cities_score;

run;

%mend;

%infra_score;

OUTPUT:

Obs	Record_Date	City_Name	Buildings	Roads_km	Public_Transport	Internet_Speed	Report_Date	Months_Since	Next_Review	Infrastructure_Score
1	01JAN2024	NewYork	1200000	14000	9	220	01MAR2024	2	01APR2024	93.0
2	05JAN2024	London	900000	9500	8	210	01MAR2024	2	05APR2024	79.5
3	10JAN2024	Tokyo	1500000	18000	10	240	01MAR2024	2	10APR2024	107.0
4	12JAN2024	Paris	800000	7000	7	190	01MAR2024	2	12APR2024	69.0
5	15JAN2024	Dubai	600000	5000	6	250	01MAR2024	2	15APR2024	66.0
6	20JAN2024	Singapor	500000	4000	9	230	01MAR2024	2	20APR2024	77.0
7	22JAN2024	Sydney	700000	8000	7	200	01MAR2024	2	22APR2024	70.0
8	25JAN2024	Mumbai	1300000	12000	6	160	01MAR2024	2	25APR2024	71.0
9	27JAN2024	Delhi	1100000	11000	6	150	01MAR2024	2	27APR2024	67.0
10	30JAN2024	Berlin	650000	6000	7	195	01MAR2024	2	30APR2024	67.0
11	01FEB2024	Toronto	750000	8500	8	205	01MAR2024	1	01MAY2024	76.5
12	03FEB2024	Seoul	1000000	9000	9	225	01MAR2024	1	03MAY2024	86.5
13	05FEB2024	HongKong	600000	3500	9	235	01MAR2024	1	05MAY2024	78.0
14	07FEB2024	LosAngel	900000	10000	7	210	01MAR2024	1	07MAY2024	75.0
15	10FEB2024	Rome	500000	4500	6	180	01MAR2024	1	10MAY2024	57.5
16	12FEB2024	Madrid	550000	5000	7	185	01MAR2024	1	12MAY2024	64.0

This macro converts different units into a single infrastructure index.

5. PROC SQL – Business Intelligence Queries

proc sql;

    create table city_ranking as

    select City_Name, Infrastructure_Score,

           case 

              when Infrastructure_Score >= 120 then "WORLD CLASS"

              when Infrastructure_Score >= 90 then "ADVANCED"

              else "DEVELOPING"

           end as City_Class

    from cities_score

    order by Infrastructure_Score desc;

quit;

proc print data=city_ranking;

run;

OUTPUT:

Obs	City_Name	Infrastructure_Score	City_Class
1	Tokyo	107.0	ADVANCED
2	NewYork	93.0	ADVANCED
3	Seoul	86.5	DEVELOPING
4	London	79.5	DEVELOPING
5	HongKong	78.0	DEVELOPING
6	Singapor	77.0	DEVELOPING
7	Toronto	76.5	DEVELOPING
8	LosAngel	75.0	DEVELOPING
9	Mumbai	71.0	DEVELOPING
10	Sydney	70.0	DEVELOPING
11	Paris	69.0	DEVELOPING
12	Berlin	67.0	DEVELOPING
13	Delhi	67.0	DEVELOPING
14	Dubai	66.0	DEVELOPING
15	Madrid	64.0	DEVELOPING
16	Rome	57.5	DEVELOPING

6. PROC MEANS – Infrastructure Statistics

proc means data=cities_score mean min max;

    var Buildings Roads_km Public_Transport Internet_Speed Infrastructure_Score;

run;

OUTPUT:

The MEANS Procedure

Variable	Mean	Minimum	Maximum
Buildings Roads_km Public_Transport Internet_Speed Infrastructure_Score	846875.00 8437.50 7.5625000 205.3125000 75.2500000	500000.00 3500.00 6.0000000 150.0000000 57.5000000	1500000.00 18000.00 10.0000000 250.0000000 107.0000000

This gives:

·       Average buildings per city

·       Average internet speed

·       Overall infrastructure quality

7. PROC CORR – Technology vs Infrastructure

proc corr data=cities_score;

    var Buildings Roads_km Public_Transport Internet_Speed Infrastructure_Score;

run;

OUTPUT:

The CORR Procedure

5 Variables:	Buildings Roads_km Public_Transport Internet_Speed Infrastructure_Score

Simple Statistics
Variable	N	Mean	Std Dev	Sum	Minimum	Maximum
Buildings	16	846875	302472	13550000	500000	1500000
Roads_km	16	8438	3974	135000	3500	18000
Public_Transport	16	7.56250	1.31498	121.00000	6.00000	10.00000
Internet_Speed	16	205.31250	28.13472	3285	150.00000	250.00000
Infrastructure_Score	16	75.25000	12.18743	1204	57.50000	107.00000

Pearson Correlation Coefficients, N = 16 Prob > |r| under H0: Rho=0
	Buildings	Roads_km	Public_Transport	Internet_Speed	Infrastructure_Score
Buildings	1.00000	0.95922 <.0001	0.27289 0.3065	-0.09193 0.7349	0.68699 0.0033
Roads_km	0.95922 <.0001	1.00000	0.31332 0.2373	-0.03410 0.9002	0.72533 0.0015
Public_Transport	0.27289 0.3065	0.31332 0.2373	1.00000	0.66166 0.0052	0.86213 <.0001
Internet_Speed	-0.09193 0.7349	-0.03410 0.9002	0.66166 0.0052	1.00000	0.55387 0.0260
Infrastructure_Score	0.68699 0.0033	0.72533 0.0015	0.86213 <.0001	0.55387 0.0260	1.00000

8. PROC SGPLOT – Visualization

proc sgplot data=cities_score;

    scatter x=Internet_Speed y=Infrastructure_Score;

    title "Internet Speed vs Infrastructure Score";

run;

OUTPUT:

This graph shows whether digital development drives overall infrastructure.

9. APPEND – Adding New Cities

data new_cities;

    format Record_Date date9.;

    input City_Name $ Buildings Roads_km Public_Transport Internet_Speed Record_Date :date9.;

    datalines;

Bangkok 700000 6500 7 190 15FEB2024

Vienna 600000 5500 8 200 18FEB2024

;

run;

proc print data=new_cities;

run;

OUTPUT:

Obs	Record_Date	City_Name	Buildings	Roads_km	Public_Transport	Internet_Speed
1	15FEB2024	Bangkok	700000	6500	7	190
2	18FEB2024	Vienna	600000	5500	8	200

proc append base=cities_raw 

            data=new_cities force;

run;

proc print data=cities_raw;

run;

OUTPUT:

Obs	Record_Date	City_Name	Buildings	Roads_km	Public_Transport	Internet_Speed
1	01JAN2024	NewYork	1200000	14000	9	220
2	05JAN2024	London	900000	9500	8	210
3	10JAN2024	Tokyo	1500000	18000	10	240
4	12JAN2024	Paris	800000	7000	7	190
5	15JAN2024	Dubai	600000	5000	6	250
6	20JAN2024	Singapor	500000	4000	9	230
7	22JAN2024	Sydney	700000	8000	7	200
8	25JAN2024	Mumbai	1300000	12000	6	160
9	27JAN2024	Delhi	1100000	11000	6	150
10	30JAN2024	Berlin	650000	6000	7	195
11	01FEB2024	Toronto	750000	8500	8	205
12	03FEB2024	Seoul	1000000	9000	9	225
13	05FEB2024	HongKong	600000	3500	9	235
14	07FEB2024	LosAngel	900000	10000	7	210
15	10FEB2024	Rome	500000	4500	6	180
16	12FEB2024	Madrid	550000	5000	7	185
17	15FEB2024	Bangkok	700000	6500	7	190
18	18FEB2024	Vienna	600000	5500	8	200

This simulates new cities joining a smart-city program.

10. SET – Combine Old and New

data all_cities;

    set cities_raw 

        new_cities;

run;

proc print data=all_cities;

run;

OUTPUT:

Obs	Record_Date	City_Name	Buildings	Roads_km	Public_Transport	Internet_Speed
1	01JAN2024	NewYork	1200000	14000	9	220
2	05JAN2024	London	900000	9500	8	210
3	10JAN2024	Tokyo	1500000	18000	10	240
4	12JAN2024	Paris	800000	7000	7	190
5	15JAN2024	Dubai	600000	5000	6	250
6	20JAN2024	Singapor	500000	4000	9	230
7	22JAN2024	Sydney	700000	8000	7	200
8	25JAN2024	Mumbai	1300000	12000	6	160
9	27JAN2024	Delhi	1100000	11000	6	150
10	30JAN2024	Berlin	650000	6000	7	195
11	01FEB2024	Toronto	750000	8500	8	205
12	03FEB2024	Seoul	1000000	9000	9	225
13	05FEB2024	HongKong	600000	3500	9	235
14	07FEB2024	LosAngel	900000	10000	7	210
15	10FEB2024	Rome	500000	4500	6	180
16	12FEB2024	Madrid	550000	5000	7	185
17	15FEB2024	Bangkok	700000	6500	7	190
18	18FEB2024	Vienna	600000	5500	8	200
19	15FEB2024	Bangkok	700000	6500	7	190
20	18FEB2024	Vienna	600000	5500	8	200

11. MERGE – Add Country Information

data country_map;

    input City_Name $ Country $;

    datalines;

NewYork USA

London UK

Tokyo Japan

Paris France

Dubai UAE

Singapore Singapore

Sydney Australia

Mumbai India

Delhi India

Berlin Germany

Toronto Canada

Seoul SouthKorea

HongKong China

LosAngeles USA

Rome Italy

Madrid Spain

Bangkok Thailand

Vienna Austria

;

run;

proc print data=country_map;

run;

OUTPUT:

Obs	City_Name	Country
1	NewYork	USA
2	London	UK
3	Tokyo	Japan
4	Paris	France
5	Dubai	UAE
6	Singapor	Singapor
7	Sydney	Australi
8	Mumbai	India
9	Delhi	India
10	Berlin	Germany
11	Toronto	Canada
12	Seoul	SouthKor
13	HongKong	China
14	LosAngel	USA
15	Rome	Italy
16	Madrid	Spain
17	Bangkok	Thailand
18	Vienna	Austria

proc sort data=all_cities; by City_Name; run;

proc print data=all_cities;

run;

OUTPUT:

Obs	Record_Date	City_Name	Buildings	Roads_km	Public_Transport	Internet_Speed
1	15FEB2024	Bangkok	700000	6500	7	190
2	15FEB2024	Bangkok	700000	6500	7	190
3	30JAN2024	Berlin	650000	6000	7	195
4	27JAN2024	Delhi	1100000	11000	6	150
5	15JAN2024	Dubai	600000	5000	6	250
6	05FEB2024	HongKong	600000	3500	9	235
7	05JAN2024	London	900000	9500	8	210
8	07FEB2024	LosAngel	900000	10000	7	210
9	12FEB2024	Madrid	550000	5000	7	185
10	25JAN2024	Mumbai	1300000	12000	6	160
11	01JAN2024	NewYork	1200000	14000	9	220
12	12JAN2024	Paris	800000	7000	7	190
13	10FEB2024	Rome	500000	4500	6	180
14	03FEB2024	Seoul	1000000	9000	9	225
15	20JAN2024	Singapor	500000	4000	9	230
16	22JAN2024	Sydney	700000	8000	7	200
17	10JAN2024	Tokyo	1500000	18000	10	240
18	01FEB2024	Toronto	750000	8500	8	205
19	18FEB2024	Vienna	600000	5500	8	200
20	18FEB2024	Vienna	600000	5500	8	200

proc sort data=country_map; by City_Name; run;

proc print data=country_map;

run;

OUTPUT:

Obs	City_Name	Country
1	Bangkok	Thailand
2	Berlin	Germany
3	Delhi	India
4	Dubai	UAE
5	HongKong	China
6	London	UK
7	LosAngel	USA
8	Madrid	Spain
9	Mumbai	India
10	NewYork	USA
11	Paris	France
12	Rome	Italy
13	Seoul	SouthKor
14	Singapor	Singapor
15	Sydney	Australi
16	Tokyo	Japan
17	Toronto	Canada
18	Vienna	Austria

data city_full;

    merge all_cities country_map;

    by City_Name;

run;

proc print data=city_full;

run;

OUTPUT:

Obs	Record_Date	City_Name	Buildings	Roads_km	Public_Transport	Internet_Speed	Country
1	15FEB2024	Bangkok	700000	6500	7	190	Thailand
2	15FEB2024	Bangkok	700000	6500	7	190	Thailand
3	30JAN2024	Berlin	650000	6000	7	195	Germany
4	27JAN2024	Delhi	1100000	11000	6	150	India
5	15JAN2024	Dubai	600000	5000	6	250	UAE
6	05FEB2024	HongKong	600000	3500	9	235	China
7	05JAN2024	London	900000	9500	8	210	UK
8	07FEB2024	LosAngel	900000	10000	7	210	USA
9	12FEB2024	Madrid	550000	5000	7	185	Spain
10	25JAN2024	Mumbai	1300000	12000	6	160	India
11	01JAN2024	NewYork	1200000	14000	9	220	USA
12	12JAN2024	Paris	800000	7000	7	190	France
13	10FEB2024	Rome	500000	4500	6	180	Italy
14	03FEB2024	Seoul	1000000	9000	9	225	SouthKor
15	20JAN2024	Singapor	500000	4000	9	230	Singapor
16	22JAN2024	Sydney	700000	8000	7	200	Australi
17	10JAN2024	Tokyo	1500000	18000	10	240	Japan
18	01FEB2024	Toronto	750000	8500	8	205	Canada
19	18FEB2024	Vienna	600000	5500	8	200	Austria
20	18FEB2024	Vienna	600000	5500	8	200	Austria

12. TRANSPOSE – Infrastructure Report Format

proc transpose data=city_ranking out=city_transpose;

    id City_Name NotSorted;

    var Infrastructure_Score;

run;

proc print data=city_transpose;

run;

OUTPUT:

Obs	_NAME_	Tokyo	NewYork	Seoul	London	HongKong	Singapor	Toronto	LosAngel	Mumbai	Sydney	Paris	Berlin	Delhi	Dubai	Madrid	Rome
1	Infrastructure_Score	107	93	86.5	79.5	78	77	76.5	75	71	70	69	67	67	66	64	57.5

This produces a horizontal city-wise infrastructure dashboard.

13. How This Mimics Real-World Urban Analytics

In real governments:

·       Buildings = urban density

·       Roads_km = transportation capacity

·       Public_Transport = mobility index

·       Internet_Speed = digital readiness

Smart-city departments compute a single composite score exactly like our macro.

Cities are then classified into:

·       Developing

·       Advanced

·       World Class

Funding and development are allocated based on this.

14. What We Learned Technically

This project teaches you:

SAS Skill	Business Meaning
DATA step	Data ingestion
MDY, INTCK, INTNX	Time-based infrastructure reviews
PROC SQL	City classification
PROC MEANS	Infrastructure averages
PROC CORR	Relationship analysis
PROC SGPLOT	City performance visualization
Macros	Automation
APPEND	Add new cities
SET	Combine datasets
MERGE	Geographic mapping
TRANSPOSE	Dashboard format

15. Why This Project Is Interview-Ready

This project mirrors:

·       Government urban development analysis

·       Smart city funding evaluation

·       Infrastructure benchmarking

·       Technology readiness assessment

If you can explain this in an interview, you demonstrate:

·       SAS programming

·       Data modeling

·       Analytics logic

·       Business thinking

16. Conclusion

We have now built a complete Smart City Infrastructure Analytics System using SAS.

This is not just coding — it is decision-making analytics similar to what urban planners, governments, and technology investors use globally.

About the Author:

SAS Learning Hub is a data analytics and SAS programming platform focused on clinical, financial, and real-world data analysis. The content is created by professionals with academic training in Pharmaceutics and hands-on experience in Base SAS, PROC SQL, Macros, SDTM, and ADaM, providing practical and industry-relevant SAS learning resources.

Disclaimer:

The datasets and analysis in this article are created for educational and demonstration purposes only. They do not represent real Infrastructure data.

Our Mission:

This blog provides industry-focused SAS programming tutorials and analytics projects covering finance, healthcare, and technology.

This project is suitable for:

SAS Programmer Interviews

SAS Programmer Job Seekers

SAS Analysts

Follow Us On : 

 

--->Follow our blog for more SAS-based analytics projects and industry data models.

To deepen your understanding of SAS analytics, please refer to our other data science and industry-focused projects listed below:

1.100+ HISTORIC BUSINESSES WORLDWIDE DATASET | PROC CONTENTS | PROC PRINT | PROC FREQ | PROC MEANS | PROC SORT | PROC REPORT| PROC SQL |MACRO SHOW_TOP_INDUSTRIES | MACRO COUNTRY_REPORT

2.ORGANIZATIONAL DATA MANAGEMENT AND SEGMENTATION IN SAS USING PROC PRINT | PROC SQL | PROC MEANS | PROC FREQ | FOR DATA CLEANING VARIABLE ENRICHMENT CONDITIONAL UPDATES | DATA MERGING AND MISSING VALUE ANALYSIS

172.GENERATING RANDOM STUDENT DATA IN SAS |  USING DATA STEP | CREATING ARRAYS |  RANDOM NUMBER GENERATION WITH RANUNI | CONDITIONAL DATA OUTPUT |  PROC PRINT FOR DATA DISPLAY | PROC FREQ FOR CLASS DISTRIBUTION  FINAL REPORT GENERATION  USING PROC REPORT

About Us | Contact | Privacy Policy