SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Transpose to a hierarchical dataset

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

Transpose to a hierarchical dataset

[ Edited ]

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


Accepted Solutions
Solution
‎08-01-2017 11:35 AM
Super User
Posts: 11,343

Re: Transpose to a hierarchical dataset

Posted in reply to Demographer

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;

View solution in original post


All Replies
Solution
‎08-01-2017 11:35 AM
Super User
Posts: 11,343

Re: Transpose to a hierarchical dataset

Posted in reply to Demographer

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 218 views
  • 0 likes
  • 2 in conversation