BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Demographer
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

1 REPLY 1
ballardw
Super User

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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