multi-label for time series

[cstarkey]

Hello.  I have a time series data set for imports and exports from IMF.  It has column one as the main country, column two as the counter country, column three as the explanation (import or export), and the rest of the columns are values, with each column representing a month.  I'm trying to transpose it but it's difficult with the 3 explanation variables (country, counter, and type).  It seems that having a multi-dimensional label would be great but I haven't found anything making me think it's possible.  Of course I could combine the columns (ie country-counter-type) but this makes it difficult when I need to sort by crisis country.  Any suggestions would be appreciated.  If Python or another language offers this, that information would be helpful also.  Thank you.  

[ballardw]

---

@cstarkey wrote:

Hello.  I have a time series data set for imports and exports from IMF.  It has column one as the main country, column two as the counter country, column three as the explanation (import or export), and the rest of the columns are values, with each column representing a month.  I'm trying to transpose it but it's difficult with the 3 explanation variables (country, counter, and type).  It seems that having a multi-dimensional label would be great but I haven't found anything making me think it's possible.  Of course I could combine the columns (ie country-counter-type) but this makes it difficult when I need to sort by crisis country.  Any suggestions would be appreciated.  If Python or another language offers this, that information would be helpful also.  Thank you.  

---

Specific examples of how you want to use this "label" would be very helpful.

SAS does have a Format variation actually called "multilabel" but only a few procedures can actually use it to full advantage: Proc Means, Summary, Tabulate and Report.

The approach would be use use the values of the sub-group as the basis.

I am attaching some code that shows  a couple of examples using a multilabel format.

[Reeza]

Please show us an example with data. I think you need multiple BY values or a data step.

[cstarkey]

Thanks for the replies.  An example would be something like:

Country     Counter Country                Type            Date        2001-01       2001-02        2001-03

France            US                               Import                            10                   11                  12

France            US                               Export                             4                     5                    6

France            UK                               Import                             1                     2                    3

France            UK                               Export                             2                     3                    4

US                   FR                               IM                                   3                     4                    5

US                   FR                               EX                                  5                     6                    7

US                   UK                               IM                                   4                     5                    6

US                   UK                               EX                                  5                     6                    7

UK                   FR                               IM

UK                   FR                               EX

UK                   US                               IM

UK                   US                               EX

This goes on for over 100 countries with numerous variables, so renaming the labels to something like US_UK_IM isn't ideal.  The idea is that I can sort by country, counter country, or type while also restricting the data to a country (such as the US for the financial crisis, China for the 2015 market event, etc) and mix those events together for my tests.  Ideally I could transpose the data set to make it long with the labels being nested so I could restrict the data to something like both the US being the country and the counter country.  

Please let me know if this explanation is helpful or not.  Thank you

[PGStats · Accepted solution]

As @Reeza suggested, you can use more than one BY variable in proc transpose (and pretty well anywhere you can use a by variable):

data have;
length Country CounterCountry Type $2;
input (Country CounterCountry Type) (:$upcase.) '2001-01'n '2001-02'n '2001-03'n;
datalines;
France US Import 10 11 12
France US Export 4 5 6
France UK Import 1 2 3
France UK Export 2 3 4
US FR IM 3 4 5
US FR EX 5 6 7
US UK IM 4 5 6
US UK EX 5 6 7
;

proc sort data=have; by Country CounterCountry Type; run;

proc transpose data=have out=temp name=varName prefix=amount;
var _numeric_;
by Country CounterCountry Type;
run;

data want;
set temp;
date = input(cats(varName,"-01"), yymmdd10.);
format date yymmd7.;
drop varName;
rename amount1=amount;
run;

[cstarkey]

Thanks for your help, I really appreciate it.  My problems transposing it were making me overcomplicate it.  That by statement is exactly what I need.  Thanks to you and Reeza for the help!