Transpose to a hierarchical dataset

[Demographer]

Hello,

I have a dataset that looks like this. Values are GNI per capita:

country	_2008	_2009	_2010	_2011	_2012	_2013	_2014
AT	48170.04	45872.71	47049.03	47966.42	47991.35	47844.27	47777.68
BE	46035.71	43666.42	45779	45000.37	45364.98	44770.25	44905.61
BG	6728.245	6576.378	6700.702	6793.838	6966.932	7004.423	7235.484
HR	14378.3	13264.8	13126.36	13518.48	13229.67	13252.88	13245.79
CY	31983.15	30817.32	29888.55	30141.42	27860.38	25836.44	25991.26
CZ	19150.05	17997.13	18277.37	18611.04	18712.49	18649.98	18959.38
DK	61171.37	57792.11	58990.78	59799.68	59800.45	60647.56	61629.64
EE	15828.32	13843.15	13862.3	15004.04	15871.73	16437.29	16874.69
FI	49456.42	45647.52	46828.41	47383.41	46516.61	45870.94	45649.71
FR	42429.01	40835.47	41534.2	42294.34	41804.55	41821.76	41976.33
DE	42770.95	40993.51	42608.81	45227.23	45285.89	45408.36	45868.56
GR	28983.92	27879.73	26366.35	23829.96	22980.53	22224.46	22528.48
HU	12958.97	12323.07	12420.71	12679.6	12630.59	13113.01	13460.7
IE	43625.79	39823.69	40638.03	39179.04	38778.99	40754.35	44227.52
IT	37243.59	35341.53	35777.9	35899.78	34837.04	33829.91	33617.25
LV	13066.97	12345.79	11468.62	12266.98	12833.45	13375.1	13799.02
LT	12939.94	11762.49	11761.63	12527.48	13273.81	13938	14740.35
LU	95792.19	71546.83	75917.07	78591.15	76144.23	79679.98	82922.72
MT	20617.45	19093.57	19982.11	20586.56	20771.2	21350.93	22960.66
NL	51144.51	49941.98	50745.9	51862.78	51238.76	50663.82	50367.63
PL	11595.81	11760.24	12148.54	12749.99	12941.03	13177.97	13567.73
PT	21954.54	21313.63	21780.64	21736.39	20838.54	20941.6	21162.84
RO	8634.634	8184.808	8172.252	8283.729	8368.645	8651.562	9040.982
SK	16432.81	15710.87	16228.34	16433.74	16975	17379.4	17664.63
SI	24857.83	22933.38	23219.28	23355.24	22699.94	22458.31	23234.84
ES	31442.24	30303.81	30305.07	29800.51	29209.03	28857.4	29400.99
SE	54631.8	50841.97	53601.5	54228.86	53811.94	53927.97	54746.72
UK	40452.03	38416.11	39207.09	39456.9	39175.35	39472.27	40086.58

I would like to transpose it to get a hierarchical origin-destination file, such as

orig	dest	year	gni_orig	gni_dest
AT	BE	2009	48170.04	46035.71
AT	BG	2009	48170.04	6728.25
…
UK	SE	2014	40086.58	54746.72

Is there a quick way to do it?

Thanks

[ballardw · Accepted solution]

How do we know BE BG etc are DEST values?

Is this actually such that you want EVERY Value of country to appear as orig and dest crossed with the others?

You might need to provide a bit more of a example of the output. You might try reducing the input data to 3 years and the countries to maybe 4 so you can hand calculate a complete example.

This may get you started. Note the provision of the data as DATA step so we can test data.

data have;
 input country $ _2008 _2009 _2010 _2011 _2012 _2013 _2014 ;
datalines;
AT 48170.04 45872.71 47049.03 47966.42 47991.35 47844.27 47777.68 
BE 46035.71 43666.42 45779 45000.37 45364.98 44770.25 44905.61 
BG 6728.245 6576.378 6700.702 6793.838 6966.932 7004.423 7235.484 
HR 14378.3 13264.8 13126.36 13518.48 13229.67 13252.88 13245.79 
CY 31983.15 30817.32 29888.55 30141.42 27860.38 25836.44 25991.26 
CZ 19150.05 17997.13 18277.37 18611.04 18712.49 18649.98 18959.38 
DK 61171.37 57792.11 58990.78 59799.68 59800.45 60647.56 61629.64 
EE 15828.32 13843.15 13862.3 15004.04 15871.73 16437.29 16874.69 
;
run;
proc sort data=have;
  by country;
run;

proc transpose data=have out=havetrans;
   by country;
run;

data ht;
  set havetrans;
  year = input (substr(_name_,2,4),best.);
  drop _name_;
  rename col1=GNI;
run;

proc sql;
   create table want as
   select a.country as orig, b.country as dest,a.year,
          a.gni as gni_orig, b.gni as gni_dest
   from ht as a, ht as b
   where a.country ne b.country and a.year=b.year
   order by a.country,b.country,a.year
   ;
run;