Editor's note: SAS programming concepts in this and other Free Data Friday articles remain useful, but SAS OnDemand for Academics has replaced SAS University Edition as a free e-learning option. Hit the orange button below to start your journey with SAS OnDemand for Academics:
These days it seems that China is constantly in the news. Whether it’s disputes alleging unfair trading practices, political unrest in Hong Kong because of Chinese influence, concerns over Chinese companies involvement in 5G telecommunications or the coronavirus outbreak, China is at the centre of it all.
One way emerging powers like China can expand their influence is by the use of so-called soft power, so in this edition of Free Day Friday we will be looking at Chinese overseas aid from aiddata.org to see which countries and sectors are the main beneficiaries of Chinese money.
The data is available as a downloadable zipped folder of CSV files from aiddata.org together with a readme file describing the contents of each CSV. I chose to use the file oda-like_flows.csv as that only contains data for projects which strictly meet the overseas aid criteria. I first used PROC Import to bring the file into SAS and it imported perfectly.
filename aidfile '/folders/myshortcuts/Dropbox/oda-like_flows.csv'; proc import datafile=aidfile dbms=csv out=chinaaid replace; getnames=yes; guessingrows=1000; run;
The file has a large number of variables and is a little more complex than usual and so I had to consult the metadata to make complete sense of it. It consists of a large number of records – each overseas aid project has a unique reference number, but there can be many records for each project and the total aid for the project is apportioned equally among the records in the even_split_commitments variable.
Most projects are single-country projects, but some cover multiple countries and so because of the difficulty of apportioning the aid among multiple recipients, I decided to remove these from the file by splitting them off to another data set. The three character recipients_iso3 variable was the one I used to determine single recipient projects – there is only one code for these, whereas for multiple country projects all codes are present, so testing the length of the text was the method I used to determine the records I wanted.
data country region; set chinaaid; if length(recipients_iso3)=3 then output country; else output region; run;
Firstly I used PROC Means to aggregate the even_split_commitments field by recipients, ad_sector_names and the combination of these two variables. Notice that when you use Class variables in PROC Means you don’t need to pre-sort the data:
proc means data=country noprint; class recipients ad_sector_names; var even_split_commitments; output out=stats sum=total_aid; types recipients ad_sector_names recipients*ad_sector_names; run;
Having done that, it was simply a matter of using PROC Rank to determine the rank for each category and then PROC Sort to sort the output taking only the top ten for each category. Firstly, for recipients:
proc rank data=stats(where=(_type_=2)) out=country_stats descending ties=high; var total_aid; ranks total_rank; run; proc sort data=country_stats(where=(total_rank ne . and total_rank<=10)); by total_rank; run;
I then used PROC SGPlot to create a horizontal bar chart of the top ten aid recipients:
proc sgplot data=country_stats; format total_aid dollar20.; title1 "Chinese Overseas Aid 2000-2014"; title2 "Top 10 Recipient Countries"; footnote j=r "Data From: www.aiddata.org"; hbar recipients / response=total_aid dataskin=gloss datalabel datalabelattrs=(weight=bold) categoryorder=respdesc; xaxis grid label="Total Aid (US Dollars)"; yaxis grid label='Recipient Country'; run;
We can see from the chart that the clear leader in the Chinese aid stakes is Cuba with over $6 billion in aid during the period. I found this somewhat surprising since, while Cuba is not a rich country, I had thought there were far poorer countries that would have been vying for top spot. I decided to investigate further and drilled into the data using PROC SQL to see what this aid was spent on:
title1 "Chinese Overseas Aid to Cuba 2000-2014"; title2 "By Category"; proc sql; select recipients, ad_sector_names, total_aid format=dollar20. from stats where recipients="Cuba" and _type_=3 order by total_aid desc; quit;
So almost all the over $6 billion aid to Cuba was “Action relating to debt”. According to Forbes, this relates to the writing off of Chinese government loans to Cuba in 2011. I wondered if the original loans were also designated as aid and if so, this debt forgiveness would seem to amount to “double counting” of the original aid sum. Unfortunately, I had no way of determining this as I can’t tell when the original loans were made to link them to the write-off.
Switching now to look at the aid by category, I carried out the same process with PROC Rank, PROC Sort and PROC SGPlot, but using a different _type_ variable value:
proc rank data=stats(where=(_type_=1)) out=sector_stats descending ties=high; var total_aid; ranks total_rank; run; proc sort data=sector_stats(where=(total_rank ne . and total_rank<=10)); by total_rank; run; proc sgplot data=sector_stats; format total_aid dollar20.; title1 "Chinese Overseas Aid 2000-2014"; title2 "Top 10 Sectors"; footnote j=r "Data From: www.aiddata.org"; hbar ad_sector_names / response=total_aid dataskin=gloss datalabel datalabelattrs=(weight=bold) categoryorder=respdesc; xaxis grid label="Total Aid (US Dollars)"; yaxis grid label='Sector'; run;
There’s another surprise here with “Transport and Storage” being the top category with over $18 billion of aid disbursed. Using PROC SQL again, I got details of which countries were the recipients of this category of aid.
title1 "Chinese Overseas 2000-2014"; title2 "Transportation and Storage"; proc sql; select recipients, ad_sector_names, total_aid format=dollar20. from stats where ad_sector_names="Transport and Storage" and _type_=3 order by total_aid desc; quit;
The top three recipients in this category all feature in the top 10 overall list above, but then we come to Sri Lanka. Looking at the individual records for Sri Lanka reveals that some (but not all) the money was loaned for the building of a port at Hambantota. The New York Times has a story on this port – it was a commercial failure and the Sri Lankan government couldn’t meet the repayments. This led to them leasing the port to the Chinese in an attempt to alleviate the country's ballooning debt problem.
Now, in fairness, the Hambantota deal has its supporters and I’m not suggesting any of these loans weren’t entered into with the best of intentions, but to me it does question the efficacy of using loans as an aid instrument when they can sometimes seem to create more problems than they solve when they come to be repaid.
Did you find something else interesting in this data? Share in the comments. I’m glad to answer any questions.
Visit [[this link]] to see all the Free Data Friday articles.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.