topic Re: multi-label for time series in SAS Programming

multi-label for time series

cstarkey — Thu, 18 Mar 2021 18:33:06 GMT

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.

Re: multi-label for time series

ballardw — Thu, 18 Mar 2021 18:45:07 GMT

@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.

Re: multi-label for time series

Reeza — Thu, 18 Mar 2021 18:47:35 GMT

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

Re: multi-label for time series

cstarkey — Fri, 19 Mar 2021 13:56:07 GMT

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

Re: multi-label for time series

PGStats — Fri, 19 Mar 2021 17:42:18 GMT

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;

Re: multi-label for time series

cstarkey — Fri, 19 Mar 2021 21:08:51 GMT

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!