topic Re: Transpose wide datasets to long datasets in SAS Programming

Transpose wide datasets to long datasets

KevinRetric — Sun, 05 Dec 2021 16:12:49 GMT

Dear Forum,

Thank you very much for your answers so far. It has been very helpful.

I would like to ask one more question concerning the transpose of wide datasets to long datasets. I have found very useful information and codes on the internet so far, however, I was wondering whether there is a more efficient code for the length of my dataset.

I would like to transpose the following dataset (the original file contains more rows and columns) ....

ID	set_FY2016	set_FY2017	set_FY2018	track_FY2016	track_FY2017	track_FY2018	Let_FY2016	Let_FY2017	Let_FY2018
1	10	20	5	12	212	3	120	204	53
2	8	0	10	4	3	32	82	3	103
3	5	40	0	55	44	3	523	440	12

... to achieve the following result:

ID	Year	Set	Track	Let
1	2016	10	12	120
1	2017	20	212	204
1	2018	5	3	53
2	2016	8	4	82
2	2017	0	3	3
...	...	...	...	...

I was trying to achieve this using the following code. Unfortunately, it returns an error message (maybe a macro can be used?):

data Want;
set Have;
array new {3} Set_ Track_ Let_;
array old {3, 2016:2018} Set_: Track_: Let_: ; /*Error Message received here: "To many variables" -- I know I can finish this code by adding theses variables manually, incl. the years , but I would like to loop/macro this process*/
do year = 2016 to 2018;
do k=1 to 3;
new{k} = old{k, year};
end;
output;
end;
keep ID Year Set Track Let;
run;

I would really appreciate your advice here - thank you very much for your input.

I wish you a nice day

Kevin

Re: Transpose wide datasets to long datasets

Reeza — Sun, 05 Dec 2021 19:07:17 GMT

IMO it would be simpler to use three arrays instead of a multidimensional array.

data Want;
set Have;

array _set {2016:2018} Set_FY2016-SET_FY2018;
array _track {2016:2018} track_FY2016-track_FY2018;

*repeat for LET;
do year = 2016 to 2018;
    SET = _set(year);
     Track = _track(year);
    *add in LET;
     output;
end;

keep ID Year Set Track Let;
run;

Details on this methodology for Wide to Long transformations:
https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

@KevinRetric wrote:

Dear Forum,

Thank you very much for your answers so far. It has been very helpful.

I would like to ask one more question concerning the transpose of wide datasets to long datasets. I have found very useful information and codes on the internet so far, however, I was wondering whether there is a more efficient code for the length of my dataset.

I would like to transpose the following dataset (the original file contains more rows and columns) ....

ID

set_FY2016

set_FY2017

set_FY2018

track_FY2016

track_FY2017

track_FY2018

Let_FY2016

Let_FY2017

Let_FY2018

1

10

20

5

12

212

3

120

204

53

2

8

0

10

4

3

32

82

3

103

3

5

40

0

55

44

3

523

440

12

... to achieve the following result:

ID

Year

Set

Track

Let

1

2016

10

12

120

1

2017

20

212

204

1

2018

5

3

53

2

2016

8

4

82

2

2017

0

3

3

...

...

...

...

...

I was trying to achieve this using the following code. Unfortunately, it returns an error message (maybe a macro can be used?):

data Want;
set Have;
array new {3} Set_ Track_ Let_;
array old {3, 2016:2018} Set_: Track_: Let_: ; /*Error Message received here: "To many variables" -- I know I can finish this code by adding theses variables manually, incl. the years , but I would like to loop/macro this process*/
do year = 2016 to 2018;
do k=1 to 3;
new{k} = old{k, year};
end;
output;
end;
keep ID Year Set Track Let;
run;

I would really appreciate your advice here - thank you very much for your input.

I wish you a nice day

Kevin

Re: Transpose wide datasets to long datasets

Tom — Sun, 05 Dec 2021 20:43:06 GMT

Why not just transpose it twice.

First convert from your wide to a TALL format.

Then move the YEAR from the name into its own variable.

Re-sort by ID and YEAR.

Then transpose form TALL into your desired format.

data have;
  input ID set_FY2016-set_FY2018 track_FY2016-track_FY2018 Let_FY2016-Let_FY2018;
cards;
1 10 20 5 12 212 3 120 204 53
2 8 0 10 4 3 32 82 3 103
3 5 40 0 55 44 3 523 440 12
;

proc transpose data=have out=tall;
  by id;
run;

data tall;
  set tall ;
  lname=length(_name_);
  year = input(substr(_name_,lname-3),32.);
  _name_ = substr(_name_,1,lname-7);
run;

proc sort;
  by id year ;
run;

proc transpose data=tall out=want(drop=_name_);
  by id year;
  id _name_;
  var col1;
run;

Result

Obs    ID    year    set    track    Let

 1      1    2016     10      12     120
 2      1    2017     20     212     204
 3      1    2018      5       3      53
 4      2    2016      8       4      82
 5      2    2017      0       3       3
 6      2    2018     10      32     103
 7      3    2016      5      55     523
 8      3    2017     40      44     440
 9      3    2018      0       3      12

Re: Transpose wide datasets to long datasets

PaigeMiller — Sun, 05 Dec 2021 21:07:35 GMT

Side comment:

Unfortunately, it returns an error message (maybe a macro can be used?):

No. If you can't get code to work without a macro and without a macro variable, then it also will not work in a macro. The solution is to fix the code, not turn it into a macro. Others have explained how to fix the code.