323.SMART CITIES ANALYSIS USING DATA STEP | PROC FORMAT | PROC SQL | PROC RANK | PROC MEANS | PROC FREQ | PROC CORR | PROC SGPLOT | MACROS WITH INTNX & INTCK

SMART CITIES ANALYSIS USING DATA STEP | PROC FORMAT | PROC SQL | PROC RANK | PROC MEANS | PROC FREQ | PROC CORR | PROC SGPLOT | MACROS WITH INTNX & INTCK

options nocenter;

1) CREATE THE MOCK DATASET

data work.smart_cities_raw;

    informat Data_Collected date9. Last_Updated date9.;

    format   Data_Collected date9. Last_Updated date9.;

    input City :$30. Country :$20. Region :$15. Population Smart_Infrastructure_Score

    Energy_Efficiency Internet_Penetration Sustainability_Index Data_Collected :date9.;

    /* Derived variables */

    Last_Updated = intnx('month', Data_Collected, 6, 'same');  /* +6 months */

    Days_Since_Collected = intck('day', Data_Collected, today());

    Energy_Efficiency_Scaled    = round(Energy_Efficiency, 0.1);

    Internet_Penetration_Pct    = round(Internet_Penetration, 0.1);

    Sustainability_Index_Scaled = round(Sustainability_Index, 0.1);

    Composite_Score = round(

                          0.4*Smart_Infrastructure_Score +

                          0.25*Energy_Efficiency_Scaled +

                          0.2*Internet_Penetration_Pct +

                          0.15*Sustainability_Index_Scaled

                           , 0.01);

    Flag_Missing = cmiss(of _numeric_);

    label Days_Since_Collected = "Days between Collection and Today()";

datalines;

Singapore       Singapore   Asia         5638700  95  88.5  98.0  90.5  01JAN2024

Copenhagen      Denmark     Europe        794128  90  85.2  96.0  88.1  15FEB2024

Amsterdam       Netherlands Europe        872757  88  82.0  95.0  87.0  01MAR2024

Tokyo           Japan       Asia        13929286  85  78.3  93.5  86.2  12JAN2024

Seoul           SouthKorea  Asia         9776000  84  76.4  92.0  85.5  20FEB2024

Barcelona       Spain       Europe       1620343  80  74.0  90.2  82.8  05MAR2024

Dubai           UAE         MiddleEast   3331420  79  68.5  88.0  78.5  10JAN2024

NewYork         USA         NorthAmerica 8419000  86  80.2  94.0  84.9  25FEB2024

Helsinki        Finland     Europe        655281  91  87.0  97.0  89.7  18MAR2024

Zurich          Switzerland Europe        434008  92  86.8  96.5  90.3  22JAN2024

Shanghai        China       Asia        26317104  82  70.1  91.0  83.5  02FEB2024

Bangalore       India       Asia         8443675  74  64.5  82.0  75.4  28FEB2024

Melbourne       Australia   Oceania      5078193  83  75.5  92.7  84.0  03MAR2024

Toronto         Canada      NorthAmerica 2731571  87  79.0  95.5  85.7  17FEB2024

Stockholm       Sweden      Europe        975904  89  84.0  96.2  88.6  07JAN2024

Reykjavik       Iceland     Europe        131136  78  81.5  98.0  86.0  11MAR2024

;

run;

proc print data=work.smart_cities_raw;

run;

OUTPUT:

Obs	Data_Collected	Last_Updated	City	Country	Region	Population	Smart_Infrastructure_Score	Energy_Efficiency	Internet_Penetration	Sustainability_Index	Days_Since_Collected	Energy_Efficiency_Scaled	Internet_Penetration_Pct	Sustainability_Index_Scaled	Composite_Score	Flag_Missing
1	01JAN2024	01JUL2024	Singapore	Singapore	Asia	5638700	95	88.5	98.0	90.5	698	88.5	98.0	90.5	93.30	1
2	15FEB2024	15AUG2024	Copenhagen	Denmark	Europe	794128	90	85.2	96.0	88.1	653	85.2	96.0	88.1	89.72	1
3	01MAR2024	01SEP2024	Amsterdam	Netherlands	Europe	872757	88	82.0	95.0	87.0	638	82.0	95.0	87.0	87.75	1
4	12JAN2024	12JUL2024	Tokyo	Japan	Asia	13929286	85	78.3	93.5	86.2	687	78.3	93.5	86.2	85.21	1
5	20FEB2024	20AUG2024	Seoul	SouthKorea	Asia	9776000	84	76.4	92.0	85.5	648	76.4	92.0	85.5	83.93	1
6	05MAR2024	05SEP2024	Barcelona	Spain	Europe	1620343	80	74.0	90.2	82.8	634	74.0	90.2	82.8	80.96	1
7	10JAN2024	10JUL2024	Dubai	UAE	MiddleEast	3331420	79	68.5	88.0	78.5	689	68.5	88.0	78.5	78.10	1
8	25FEB2024	25AUG2024	NewYork	USA	NorthAmerica	8419000	86	80.2	94.0	84.9	643	80.2	94.0	84.9	85.99	1
9	18MAR2024	18SEP2024	Helsinki	Finland	Europe	655281	91	87.0	97.0	89.7	621	87.0	97.0	89.7	91.01	1
10	22JAN2024	22JUL2024	Zurich	Switzerland	Europe	434008	92	86.8	96.5	90.3	677	86.8	96.5	90.3	91.35	1
11	02FEB2024	02AUG2024	Shanghai	China	Asia	26317104	82	70.1	91.0	83.5	666	70.1	91.0	83.5	81.05	1
12	28FEB2024	28AUG2024	Bangalore	India	Asia	8443675	74	64.5	82.0	75.4	640	64.5	82.0	75.4	73.44	1
13	03MAR2024	03SEP2024	Melbourne	Australia	Oceania	5078193	83	75.5	92.7	84.0	636	75.5	92.7	84.0	83.22	1
14	17FEB2024	17AUG2024	Toronto	Canada	NorthAmerica	2731571	87	79.0	95.5	85.7	651	79.0	95.5	85.7	86.51	1
15	07JAN2024	07JUL2024	Stockholm	Sweden	Europe	975904	89	84.0	96.2	88.6	692	84.0	96.2	88.6	89.13	1
16	11MAR2024	11SEP2024	Reykjavik	Iceland	Europe	131136	78	81.5	98.0	86.0	628	81.5	98.0	86.0	84.08	1

2) FORMATS + PROC FORMAT

proc format;

    value $regionfmt

        'Asia' = 'Asia'

        'Europe' = 'Europe'

        'NorthAmerica' = 'North America'

        'Oceania' = 'Oceania'

        'MiddleEast' = 'Middle East'

        other = 'Other';

run;

LOG:

NOTE: Format $REGIONFMT has been output.

3) MACRO: CATEGORIZE & RANK

%macro CatRank(indata=work.smart_cities_raw, out=work.smart_cities_ranked, topcut=90, 

               highcut=80, mediumcut=70);

    data &out.;

        set &indata.;

        length Category $10.;

        if Composite_Score >= &topcut. then Category='Top';

        else if Composite_Score >= &highcut. then Category='High';

        else if Composite_Score >= &mediumcut. then Category='Medium';

        else Category='Low';

    run;

    proc print data=&out.;

    run;

    /* Create a rank variable using PROC RANK (descending) */

    proc rank data=&out. out=&out. ties=low descending; 

        var Composite_Score; ranks Rank_Composite;

    run;

    /* Create an overall rank starting from 1 */

    data &out.;

        set &out.;

        Rank_Composite = Rank_Composite + 1; /* make ranks start at 1 */

    run;

    proc print data=&out.;

    run;

    /* PROC SQL summary by Region */

    proc sql;

        create table work.region_summary as

        select Region, count(*) as N_Cities,

               mean(Composite_Score) as Mean_Composite format=8.2,

               min(Composite_Score) as Min_Composite,

               max(Composite_Score) as Max_Composite

        from &out.

        group by Region

        order by Mean_Composite desc;

    quit;

  

    %put NOTE: CatRank macro completed. Output dataset=&out. and work.region_summary created.;

%mend CatRank;

%CatRank();

OUTPUT:

Obs	Data_Collected	Last_Updated	City	Country	Region	Population	Smart_Infrastructure_Score	Energy_Efficiency	Internet_Penetration	Sustainability_Index	Days_Since_Collected	Energy_Efficiency_Scaled	Internet_Penetration_Pct	Sustainability_Index_Scaled	Composite_Score	Flag_Missing	Category
1	01JAN2024	01JUL2024	Singapore	Singapore	Asia	5638700	95	88.5	98.0	90.5	698	88.5	98.0	90.5	93.30	1	Top
2	15FEB2024	15AUG2024	Copenhagen	Denmark	Europe	794128	90	85.2	96.0	88.1	653	85.2	96.0	88.1	89.72	1	High
3	01MAR2024	01SEP2024	Amsterdam	Netherlands	Europe	872757	88	82.0	95.0	87.0	638	82.0	95.0	87.0	87.75	1	High
4	12JAN2024	12JUL2024	Tokyo	Japan	Asia	13929286	85	78.3	93.5	86.2	687	78.3	93.5	86.2	85.21	1	High
5	20FEB2024	20AUG2024	Seoul	SouthKorea	Asia	9776000	84	76.4	92.0	85.5	648	76.4	92.0	85.5	83.93	1	High
6	05MAR2024	05SEP2024	Barcelona	Spain	Europe	1620343	80	74.0	90.2	82.8	634	74.0	90.2	82.8	80.96	1	High
7	10JAN2024	10JUL2024	Dubai	UAE	MiddleEast	3331420	79	68.5	88.0	78.5	689	68.5	88.0	78.5	78.10	1	Medium
8	25FEB2024	25AUG2024	NewYork	USA	NorthAmerica	8419000	86	80.2	94.0	84.9	643	80.2	94.0	84.9	85.99	1	High
9	18MAR2024	18SEP2024	Helsinki	Finland	Europe	655281	91	87.0	97.0	89.7	621	87.0	97.0	89.7	91.01	1	Top
10	22JAN2024	22JUL2024	Zurich	Switzerland	Europe	434008	92	86.8	96.5	90.3	677	86.8	96.5	90.3	91.35	1	Top
11	02FEB2024	02AUG2024	Shanghai	China	Asia	26317104	82	70.1	91.0	83.5	666	70.1	91.0	83.5	81.05	1	High
12	28FEB2024	28AUG2024	Bangalore	India	Asia	8443675	74	64.5	82.0	75.4	640	64.5	82.0	75.4	73.44	1	Medium
13	03MAR2024	03SEP2024	Melbourne	Australia	Oceania	5078193	83	75.5	92.7	84.0	636	75.5	92.7	84.0	83.22	1	High
14	17FEB2024	17AUG2024	Toronto	Canada	NorthAmerica	2731571	87	79.0	95.5	85.7	651	79.0	95.5	85.7	86.51	1	High
15	07JAN2024	07JUL2024	Stockholm	Sweden	Europe	975904	89	84.0	96.2	88.6	692	84.0	96.2	88.6	89.13	1	High
16	11MAR2024	11SEP2024	Reykjavik	Iceland	Europe	131136	78	81.5	98.0	86.0	628	81.5	98.0	86.0	84.08	1	High

Obs	Data_Collected	Last_Updated	City	Country	Region	Population	Smart_Infrastructure_Score	Energy_Efficiency	Internet_Penetration	Sustainability_Index	Days_Since_Collected	Energy_Efficiency_Scaled	Internet_Penetration_Pct	Sustainability_Index_Scaled	Composite_Score	Flag_Missing	Category	Rank_Composite
1	01JAN2024	01JUL2024	Singapore	Singapore	Asia	5638700	95	88.5	98.0	90.5	698	88.5	98.0	90.5	93.30	1	Top	2
2	15FEB2024	15AUG2024	Copenhagen	Denmark	Europe	794128	90	85.2	96.0	88.1	653	85.2	96.0	88.1	89.72	1	High	5
3	01MAR2024	01SEP2024	Amsterdam	Netherlands	Europe	872757	88	82.0	95.0	87.0	638	82.0	95.0	87.0	87.75	1	High	7
4	12JAN2024	12JUL2024	Tokyo	Japan	Asia	13929286	85	78.3	93.5	86.2	687	78.3	93.5	86.2	85.21	1	High	10
5	20FEB2024	20AUG2024	Seoul	SouthKorea	Asia	9776000	84	76.4	92.0	85.5	648	76.4	92.0	85.5	83.93	1	High	12
6	05MAR2024	05SEP2024	Barcelona	Spain	Europe	1620343	80	74.0	90.2	82.8	634	74.0	90.2	82.8	80.96	1	High	15
7	10JAN2024	10JUL2024	Dubai	UAE	MiddleEast	3331420	79	68.5	88.0	78.5	689	68.5	88.0	78.5	78.10	1	Medium	16
8	25FEB2024	25AUG2024	NewYork	USA	NorthAmerica	8419000	86	80.2	94.0	84.9	643	80.2	94.0	84.9	85.99	1	High	9
9	18MAR2024	18SEP2024	Helsinki	Finland	Europe	655281	91	87.0	97.0	89.7	621	87.0	97.0	89.7	91.01	1	Top	4
10	22JAN2024	22JUL2024	Zurich	Switzerland	Europe	434008	92	86.8	96.5	90.3	677	86.8	96.5	90.3	91.35	1	Top	3
11	02FEB2024	02AUG2024	Shanghai	China	Asia	26317104	82	70.1	91.0	83.5	666	70.1	91.0	83.5	81.05	1	High	14
12	28FEB2024	28AUG2024	Bangalore	India	Asia	8443675	74	64.5	82.0	75.4	640	64.5	82.0	75.4	73.44	1	Medium	17
13	03MAR2024	03SEP2024	Melbourne	Australia	Oceania	5078193	83	75.5	92.7	84.0	636	75.5	92.7	84.0	83.22	1	High	13
14	17FEB2024	17AUG2024	Toronto	Canada	NorthAmerica	2731571	87	79.0	95.5	85.7	651	79.0	95.5	85.7	86.51	1	High	8
15	07JAN2024	07JUL2024	Stockholm	Sweden	Europe	975904	89	84.0	96.2	88.6	692	84.0	96.2	88.6	89.13	1	High	6
16	11MAR2024	11SEP2024	Reykjavik	Iceland	Europe	131136	78	81.5	98.0	86.0	628	81.5	98.0	86.0	84.08	1	High	11

proc print data=work.smart_cities_ranked;

 run;

OUTPUT:

Obs	Data_Collected	Last_Updated	City	Country	Region	Population	Smart_Infrastructure_Score	Energy_Efficiency	Internet_Penetration	Sustainability_Index	Days_Since_Collected	Energy_Efficiency_Scaled	Internet_Penetration_Pct	Sustainability_Index_Scaled	Composite_Score	Flag_Missing	Category	Rank_Composite
1	01JAN2024	01JUL2024	Singapore	Singapore	Asia	5638700	95	88.5	98.0	90.5	698	88.5	98.0	90.5	93.30	1	Top	2
2	15FEB2024	15AUG2024	Copenhagen	Denmark	Europe	794128	90	85.2	96.0	88.1	653	85.2	96.0	88.1	89.72	1	High	5
3	01MAR2024	01SEP2024	Amsterdam	Netherlands	Europe	872757	88	82.0	95.0	87.0	638	82.0	95.0	87.0	87.75	1	High	7
4	12JAN2024	12JUL2024	Tokyo	Japan	Asia	13929286	85	78.3	93.5	86.2	687	78.3	93.5	86.2	85.21	1	High	10
5	20FEB2024	20AUG2024	Seoul	SouthKorea	Asia	9776000	84	76.4	92.0	85.5	648	76.4	92.0	85.5	83.93	1	High	12
6	05MAR2024	05SEP2024	Barcelona	Spain	Europe	1620343	80	74.0	90.2	82.8	634	74.0	90.2	82.8	80.96	1	High	15
7	10JAN2024	10JUL2024	Dubai	UAE	MiddleEast	3331420	79	68.5	88.0	78.5	689	68.5	88.0	78.5	78.10	1	Medium	16
8	25FEB2024	25AUG2024	NewYork	USA	NorthAmerica	8419000	86	80.2	94.0	84.9	643	80.2	94.0	84.9	85.99	1	High	9
9	18MAR2024	18SEP2024	Helsinki	Finland	Europe	655281	91	87.0	97.0	89.7	621	87.0	97.0	89.7	91.01	1	Top	4
10	22JAN2024	22JUL2024	Zurich	Switzerland	Europe	434008	92	86.8	96.5	90.3	677	86.8	96.5	90.3	91.35	1	Top	3
11	02FEB2024	02AUG2024	Shanghai	China	Asia	26317104	82	70.1	91.0	83.5	666	70.1	91.0	83.5	81.05	1	High	14
12	28FEB2024	28AUG2024	Bangalore	India	Asia	8443675	74	64.5	82.0	75.4	640	64.5	82.0	75.4	73.44	1	Medium	17
13	03MAR2024	03SEP2024	Melbourne	Australia	Oceania	5078193	83	75.5	92.7	84.0	636	75.5	92.7	84.0	83.22	1	High	13
14	17FEB2024	17AUG2024	Toronto	Canada	NorthAmerica	2731571	87	79.0	95.5	85.7	651	79.0	95.5	85.7	86.51	1	High	8
15	07JAN2024	07JUL2024	Stockholm	Sweden	Europe	975904	89	84.0	96.2	88.6	692	84.0	96.2	88.6	89.13	1	High	6
16	11MAR2024	11SEP2024	Reykjavik	Iceland	Europe	131136	78	81.5	98.0	86.0	628	81.5	98.0	86.0	84.08	1	High	11

proc print data=work.region_summary; 

run;

OUTPUT:

Obs	Region	N_Cities	Mean_Composite	Min_Composite	Max_Composite
1	Europe	7	87.71	80.96	91.35
2	NorthAmerica	2	86.25	85.99	86.51
3	Asia	5	83.39	73.44	93.30
4	Oceania	1	83.22	83.22	83.22
5	MiddleEast	1	78.10	78.10	78.10

4) PROC SQL additional transformations and views

proc sql outobs=10;

    create table work.top10_smart as

    select City, Country, Region, Population, Composite_Score, Rank_Composite

    from work.smart_cities_ranked

    order by Composite_Score desc;

quit;

proc print data=work.top10_smart;

run;

OUTPUT:

Obs	City	Country	Region	Population	Composite_Score	Rank_Composite
1	Singapore	Singapore	Asia	5638700	93.30	2
2	Zurich	Switzerland	Europe	434008	91.35	3
3	Helsinki	Finland	Europe	655281	91.01	4
4	Copenhagen	Denmark	Europe	794128	89.72	5
5	Stockholm	Sweden	Europe	975904	89.13	6
6	Amsterdam	Netherlands	Europe	872757	87.75	7
7	Toronto	Canada	NorthAmerica	2731571	86.51	8
8	NewYork	USA	NorthAmerica	8419000	85.99	9
9	Tokyo	Japan	Asia	13929286	85.21	10
10	Reykjavik	Iceland	Europe	131136	84.08	11

5) DESCRIPTIVE STATS: PROC MEANS and PROC FREQ

proc means data=work.smart_cities_ranked n mean std min max median maxdec=2;

    var Smart_Infrastructure_Score Energy_Efficiency_Scaled Internet_Penetration_Pct 

        Sustainability_Index_Scaled Composite_Score Population;

    output out=work.summary_stats mean= / autoname;

run;

proc print data=work.summary_stats;

run;

OUTPUT:

The MEANS Procedure

Variable	N	Mean	Std Dev	Minimum	Maximum	Median
Smart_Infrastructure_Score Energy_Efficiency_Scaled Internet_Penetration_Pct Sustainability_Index_Scaled Composite_Score Population	16 16 16 16 16 16	85.19 78.84 93.48 85.42 85.30 5571781.63	5.68 7.02 4.20 4.08 5.24 6876977.86	74.00 64.50 82.00 75.40 73.44 131136.00	95.00 88.50 98.00 90.50 93.30 26317104.00	85.50 79.60 94.50 85.85 85.60 3031495.50

Obs	_TYPE_	_FREQ_	Smart_Infrastructure_Scor_Mean	Energy_Efficiency_Scaled_Mean	Internet_Penetration_Pct_Mean	Sustainability_Index_Scal_Mean	Composite_Score_Mean	Population_Mean
1	0	16	85.1875	78.8438	93.475	85.4188	85.2969	5571781.63

proc freq data=work.smart_cities_ranked order=freq;

    tables Category Region / nocum nopercent;

run;

OUTPUT:

The FREQ Procedure

Category	Frequency
High	11
Top	3
Medium	2

Region	Frequency
Europe	7
Asia	5
NorthAmerica	2
MiddleEast	1
Oceania	1

6) CORRELATION ANALYSIS: PROC CORR

proc corr data=work.smart_cities_ranked nosimple plots=matrix(histogram);

    var Smart_Infrastructure_Score Energy_Efficiency_Scaled Internet_Penetration_Pct 

        Sustainability_Index_Scaled Composite_Score Population;

run;

OUTPUT:

The CORR Procedure

6 Variables:	Smart_Infrastructure_Score Energy_Efficiency_Scaled Internet_Penetration_Pct Sustainability_Index_Scaled Composite_Score Population

Pearson Correlation Coefficients, N = 16 Prob > |r| under H0: Rho=0
	Smart_Infrastructure_Score	Energy_Efficiency_Scaled	Internet_Penetration_Pct	Sustainability_Index_Scaled	Composite_Score	Population
Smart_Infrastructure_Score	1.00000	0.87837 <.0001	0.76706 0.0005	0.89342 <.0001	0.95454 <.0001	-0.25546 0.3396
Energy_Efficiency_Scaled	0.87837 <.0001	1.00000	0.92628 <.0001	0.95639 <.0001	0.97544 <.0001	-0.48850 0.0549
Internet_Penetration_Pct	0.76706 0.0005	0.92628 <.0001	1.00000	0.93697 <.0001	0.91204 <.0001	-0.36466 0.1649
Sustainability_Index_Scaled	0.89342 <.0001	0.95639 <.0001	0.93697 <.0001	1.00000	0.97414 <.0001	-0.28797 0.2794
Composite_Score	0.95454 <.0001	0.97544 <.0001	0.91204 <.0001	0.97414 <.0001	1.00000	-0.36627 0.1629
Population	-0.25546 0.3396	-0.48850 0.0549	-0.36466 0.1649	-0.28797 0.2794	-0.36627 0.1629	1.00000

7) VISUALIZATION: PROC SGPLOT (Top 10 by Composite Score)

proc sgplot data=work.top10_smart;

    vbarparm category=City response=Composite_Score / datalabel;

    xaxis label='City (Top 10)';

    yaxis label='Composite Score';

    title 'Top 10 Smart Cities by Composite Score';

run;

OUTPUT:

/* Another plot: scatter of Internet_Penetration vs Composite_Score */

proc sgplot data=work.smart_cities_ranked;

    scatter x=Internet_Penetration_Pct y=Composite_Score / datalabel=City;

    reg x=Internet_Penetration_Pct y=Composite_Score; /* add regression */

    xaxis label='Internet Penetration (%)';

    yaxis label='Composite Score';

    title 'Composite Score vs Internet Penetration';

run;

OUTPUT:

8) USING INTCK and INTNX in reporting: create monthly buckets and age of data

data work.smart_cities_time;

    set work.smart_cities_ranked;

    /* bucket = first day of collection month */

    Collection_Month = intnx('month', Data_Collected, 0, 'B');

    format Collection_Month monyy7.;

    Months_Since_Collection = intck('month', Collection_Month, today());

run;

proc print data=work.smart_cities_time; 

  var City Data_Collected Collection_Month Months_Since_Collection;

run;

OUTPUT:

Obs	City	Data_Collected	Collection_Month	Months_Since_Collection
1	Singapore	01JAN2024	JAN2024	22
2	Copenhagen	15FEB2024	FEB2024	21
3	Amsterdam	01MAR2024	MAR2024	20
4	Tokyo	12JAN2024	JAN2024	22
5	Seoul	20FEB2024	FEB2024	21
6	Barcelona	05MAR2024	MAR2024	20
7	Dubai	10JAN2024	JAN2024	22
8	NewYork	25FEB2024	FEB2024	21
9	Helsinki	18MAR2024	MAR2024	20
10	Zurich	22JAN2024	JAN2024	22
11	Shanghai	02FEB2024	FEB2024	21
12	Bangalore	28FEB2024	FEB2024	21
13	Melbourne	03MAR2024	MAR2024	20
14	Toronto	17FEB2024	FEB2024	21
15	Stockholm	07JAN2024	JAN2024	22
16	Reykjavik	11MAR2024	MAR2024	20

/* Frequency of collection months */

proc freq data=work.smart_cities_time; 

  tables Collection_Month / nocum; 

  format Collection_Month monyy7.;

run;

OUTPUT:

The FREQ Procedure

Collection_Month	Frequency	Percent
JAN2024	5	31.25
FEB2024	6	37.50
MAR2024	5	31.25

9) EXTENSIONS: Additional macros and automation examples

%macro region_reports(indata=work.smart_cities_ranked);

    proc sql noprint;

        select distinct Region 

        into :rlist separated by '|' 

        from &indata.;

    quit;

    %let nregions=%sysfunc(countw(&rlist., |));

    %do i=1 %to &nregions.;

        %let region=%scan(&rlist., &i., |);

        %put Generating report for &region.;

        data work._region_&i.;

            set &indata.;

            where Region = "&region.";

        run;

proc print data=work._region_&i.;

run;

    %end;

%mend region_reports;

%region_reports();

OUTPUT:

Obs	Data_Collected	Last_Updated	City	Country	Region	Population	Smart_Infrastructure_Score	Energy_Efficiency	Internet_Penetration	Sustainability_Index	Days_Since_Collected	Energy_Efficiency_Scaled	Internet_Penetration_Pct	Sustainability_Index_Scaled	Composite_Score	Flag_Missing	Category	Rank_Composite
1	01JAN2024	01JUL2024	Singapore	Singapore	Asia	5638700	95	88.5	98.0	90.5	698	88.5	98.0	90.5	93.30	1	Top	2
2	12JAN2024	12JUL2024	Tokyo	Japan	Asia	13929286	85	78.3	93.5	86.2	687	78.3	93.5	86.2	85.21	1	High	10
3	20FEB2024	20AUG2024	Seoul	SouthKorea	Asia	9776000	84	76.4	92.0	85.5	648	76.4	92.0	85.5	83.93	1	High	12
4	02FEB2024	02AUG2024	Shanghai	China	Asia	26317104	82	70.1	91.0	83.5	666	70.1	91.0	83.5	81.05	1	High	14
5	28FEB2024	28AUG2024	Bangalore	India	Asia	8443675	74	64.5	82.0	75.4	640	64.5	82.0	75.4	73.44	1	Medium	17

Obs	Data_Collected	Last_Updated	City	Country	Region	Population	Smart_Infrastructure_Score	Energy_Efficiency	Internet_Penetration	Sustainability_Index	Days_Since_Collected	Energy_Efficiency_Scaled	Internet_Penetration_Pct	Sustainability_Index_Scaled	Composite_Score	Flag_Missing	Category	Rank_Composite
1	15FEB2024	15AUG2024	Copenhagen	Denmark	Europe	794128	90	85.2	96.0	88.1	653	85.2	96.0	88.1	89.72	1	High	5
2	01MAR2024	01SEP2024	Amsterdam	Netherlands	Europe	872757	88	82.0	95.0	87.0	638	82.0	95.0	87.0	87.75	1	High	7
3	05MAR2024	05SEP2024	Barcelona	Spain	Europe	1620343	80	74.0	90.2	82.8	634	74.0	90.2	82.8	80.96	1	High	15
4	18MAR2024	18SEP2024	Helsinki	Finland	Europe	655281	91	87.0	97.0	89.7	621	87.0	97.0	89.7	91.01	1	Top	4
5	22JAN2024	22JUL2024	Zurich	Switzerland	Europe	434008	92	86.8	96.5	90.3	677	86.8	96.5	90.3	91.35	1	Top	3
6	07JAN2024	07JUL2024	Stockholm	Sweden	Europe	975904	89	84.0	96.2	88.6	692	84.0	96.2	88.6	89.13	1	High	6
7	11MAR2024	11SEP2024	Reykjavik	Iceland	Europe	131136	78	81.5	98.0	86.0	628	81.5	98.0	86.0	84.08	1	High	11

Obs	Data_Collected	Last_Updated	City	Country	Region	Population	Smart_Infrastructure_Score	Energy_Efficiency	Internet_Penetration	Sustainability_Index	Days_Since_Collected	Energy_Efficiency_Scaled	Internet_Penetration_Pct	Sustainability_Index_Scaled	Composite_Score	Flag_Missing	Category	Rank_Composite
1	10JAN2024	10JUL2024	Dubai	UAE	MiddleEast	3331420	79	68.5	88	78.5	689	68.5	88	78.5	78.1	1	Medium	16

Obs	Data_Collected	Last_Updated	City	Country	Region	Population	Smart_Infrastructure_Score	Energy_Efficiency	Internet_Penetration	Sustainability_Index	Days_Since_Collected	Energy_Efficiency_Scaled	Internet_Penetration_Pct	Sustainability_Index_Scaled	Composite_Score	Flag_Missing	Category	Rank_Composite
1	25FEB2024	25AUG2024	NewYork	USA	NorthAmerica	8419000	86	80.2	94.0	84.9	643	80.2	94.0	84.9	85.99	1	High	9
2	17FEB2024	17AUG2024	Toronto	Canada	NorthAmerica	2731571	87	79.0	95.5	85.7	651	79.0	95.5	85.7	86.51	1	High	8

Obs	Data_Collected	Last_Updated	City	Country	Region	Population	Smart_Infrastructure_Score	Energy_Efficiency	Internet_Penetration	Sustainability_Index	Days_Since_Collected	Energy_Efficiency_Scaled	Internet_Penetration_Pct	Sustainability_Index_Scaled	Composite_Score	Flag_Missing	Category	Rank_Composite
1	03MAR2024	03SEP2024	Melbourne	Australia	Oceania	5078193	83	75.5	92.7	84	636	75.5	92.7	84	83.22	1	High	13

To Visit My Previous Original Data Management Dataset:Click Here

To Visit My Previous Cricket World Cup Dataset:Click Here

To Visit My Previous Home Tour Dataset:Click Here

To Visit My Previous Real-World Dal Price Dataset:Click Here  

Follow Us On : 

 

--- FOLLOW OUR BLOG FOR MORE INFORMATION.

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