/*
SAS software program to read in the four Combined On-line Information System (COINS) files as released by HM Treasury.
Program: COINS Data Read.sas
Written by: Andrew Eaves
Andalus Solutions Limited
e: andrew@andalus-solutions.com
m: 07905 521113
w: andalus-solutions.com
SAS version: v9.2
Updated: 5 June 2010
COINS is used by the Treasury to collect financial data from across the public sector to support fiscal management, the production of Parliamentary
Supply Estimates and public expenditure statistics, the preparation of Whole of Government Accounts (WGA) and to meet data requirements of the Office
for National Statistics (ONS).
The COINS data are provided in two files for each financial year; the ‘fact table’ (fact table extract 200x xx.txt) and the ‘adjustment table’
(adjustment table extract 200x xx .txt). Currently two years of data (four csv files) are available to download from http://www.hm-treasury.gov.uk/coins.
You will need to download the files and uncompress them into a specified folder.
*/
* Amend the following line to point to the folder where the raw data is held. ;
%let RootDir = C:\My Documents\COINS;
* Specify number of rows to read in (use max for all). ;
%let NumRows = 10;
* Read in the data for each of the two years. ;
%macro InData(year);
* Read in the Fact Table extracts. ;
data FTExt_&year;
infile "&RootDir\fact_table_extract_20&year..txt" dsd dlm='@' missover firstobs=2 obs=&NumRows lrecl=1000;
input Data_type :$20. Data_type_description :$20. Department_code :$20. Department_description :$20.
Account_code :$20. Account_description :$20. data_subtype :$20. data_subtype_description :$20. Time :$20. Counterparty_code :$20. Couterparty_description :$20.
Programme_object_code :$20. Programme_object_description :$20. Programme_object_group_code :$20. Programme_object_group_desc :$20. Accounting_Authority :$20.
Accounts_capital_current :$20. Activity_code :$20. Budget_Boundary :$20. Budget_capital_current :$20. Resource_Capital :$20. Programme_admin :$20. CGA_Body_Type :$20.
COFOG :$20. Dept_Group :$20. Estimate_line :$20. Estimate_line_last_year :$20. Estimate_line_next_year :$20. ESA :$20. Estimates_AinA :$20.
Estimates_capital_current :$20. EU :$20. Income_Category :$20. LG :$20. LG_Body_Type :$20. Estimate_number :$20. Estimate_number_last_year :$20.
Estimate_number_next_year :$20. NAC :$20. Near_Cash_Non_Cash :$20. NHS_Body_Type :$20. PC_Body_Type :$20. PESA :$20. PESA_1_1 :$20. PESA_AEF_Grants :$20.
PESA_Capital_Support :$20. PESA_Current_Grants :$20. PESA_Delivery :$20. PESA_Non_AEF_Grants :$20. PESA_Services :$20. PESA_Tables :$20. PESA_Transfer :$20.
Request_for_resources :$20. Request_for_resources_last_year :$20. Request_for_resources_next_year :$20. SBI :$20. Sector :$20. SIGNAGE :$20. Territory :$20.
Cbal :$20. Grant_Provision :$20. Levy_Funded :$20. Local_Government_Use_only :$20. Net_Subhead :$20. Non_ID_Exceptions :$20. NotOCS :$20. Obal :$20. Outside_TES :$20.
Pension :$20. PESA_1_1_CAP :$20. PESA_1_1_EC_Payments :$20. PESA_1_1_Local_Exp :$20. PESA_1_1_Nat_Lottery :$20. PESA_1_1_Tax_Credits :$20. PESA_1_2_NHS :$20.
PESA_1_4_Locally_Financed :$20. PESA_BBC :$20. PESA_STU_LOANS :$20. Social_Fund :$20. Trust_Debt_Remuneration :$20. Value;
run;
* Read in the Adjustment Table data. ;
data AdjTab_&year;
infile "&RootDir\adjustment_table_extract_20&year..txt" dsd dlm='@' missover firstobs=2 obs=&NumRows lrecl=1000;
input Adjustment_identifier :$20. Adjustment_type :$20. Stage :$20. Title :$20. Account_code :$20. Account_description :$20. Line_description :$20. Department_code :$20. Department_description :$20.
Time :$20. Counterparty_code :$20. Couterparty_description :$20. Programme_object_code :$20. Programme_object_description :$20. Data_type :$20. Data_type_description :$20. Accounting_Authority :$20.
Accounts_capital_current :$20. Activity_code :$20. Budget_Boundary :$20. Budget_capital_current :$20. Resource_Capital :$20. Programme_admin :$20. CGA_Body_Type :$20. COFOG :$20. Dept_Group :$20.
Estimate_line :$20. Estimate_line_last_year :$20. Estimate_line_next_year :$20. ESA :$20. Estimates_AinA :$20. Estimates_capital_current :$20. EU :$20. Income_Category :$20. LG :$20. LG_Body_Type :$20.
Estimate_number :$20. Estimate_number_last_year :$20. Estimate_number_next_year :$20. NAC :$20. Near_Cash_Non_Cash :$20. NHS_Body_Type :$20. PC_Body_Type :$20. PESA :$20. PESA_1_1 :$20. PESA_AEF_Grants :$20.
PESA_Capital_Support :$20. PESA_Current_Grants :$20. PESA_Delivery :$20. PESA_Non_AEF_Grants :$20. PESA_Services :$20. PESA_Tables :$20. PESA_Transfer :$20. Request_for_resources :$20.
Request_for_resources_last_year :$20. Request_for_resources_next_year :$20. SBI :$20. Sector :$20. SIGNAGE :$20. Territory :$20. Cbal :$20. Grant_Provision :$20. Levy_Funded :$20.
Local_Government_Use_only :$20. Net_Subhead :$20. Non_ID_Exceptions :$20. NotOCS :$20. Obal :$20. Outside_TES :$20. Pension :$20. PESA_1_1_CAP :$20. PESA_1_1_EC_Payments :$20. PESA_1_1_Local_Exp :$20.
PESA_1_1_Nat_Lottery :$20. PESA_1_1_Tax_Credits :$20. PESA_1_2_NHS :$20. PESA_1_4_Locally_Financed :$20. PESA_BBC :$20. PESA_STU_LOANS :$20. Social_Fund :$20. Trust_Debt_Remuneration :$20. amount;
run;
%mend InData;
%InData(08_09);
%InData(09_10);
|
