topic Re: How to TRANSPOSE Orderly? in SAS Programming

How to TRANSPOSE Orderly?

Junyong — Tue, 01 Dec 2020 04:05:35 GMT

I want to transpose the following data set—the following is the working example, but the real data set has more than five is (and the is are non-continuous).

data have;
	input i t x;
cards;
1 1 -0.43
1 2 1.45
1 3 0.78
2 3 0.43
2 4 -0.76
2 5 0.08
2 6 0.31
2 7 -1.39
3 2 -1.79
3 3 0.62
3 4 0.66
3 5 -0.96
3 6 0.29
3 7 -2.13
3 8 -0.58
3 9 -2.59
3 10 -0.34
3 11 -0.17
3 12 -0.66
4 1 -2.28
4 2 1.8
4 3 1.45
4 4 -1.19
4 5 1.13
4 6 0.59
4 7 -0.67
5 3 -0.92
5 4 -0.84
5 5 -0.11
5 6 -1.47
;

The easiest way may be sort and then transpose as follows.

proc sort out=now;
	by t i;
run;

proc transpose prefix=x out=now(drop=_name_);
	by t;
	id i;
run;

The problem is the resultant variable order—transpose locates x4 first because x2 and x3 have no observation at t=1.

t	x1	x4	x3	x2	x5
1	-0.43	-2.28
2	1.45	1.8	-1.79
3	0.78	1.45	0.62	0.43	-0.92
4		-1.19	0.66	-0.76	-0.84
5		1.13	-0.96	0.08	-0.11
6		0.59	0.29	0.31	-1.47
7		-0.67	-2.13	-1.39
8			-0.58
9			-2.59
10			-0.34
11			-0.17
12			-0.66

The following code orderly locates all the is but will be inconvenient if there are too many is I need to code.

data want(drop=i);
	merge have(where=(i=1) rename=(x=x1))
		have(where=(i=2) rename=(x=x2))
		have(where=(i=3) rename=(x=x3))
		have(where=(i=4) rename=(x=x4))
		have(where=(i=5) rename=(x=x5));
	by t;
run;

Or I can use longer %macro as follows, but this version will be inaccurate as well if the macro variable list exceeds 65,534 characters.

%macro long;

proc sql noprint;
	select distinct i into :i separated by " " from have;
quit;

data long(drop=i);
	merge%do j=1 %to %sysfunc(countw(&i)); have(where=(i=%scan(&i,&j)) rename=(x=x%scan(&i,&j)))%end;;
	by t;
run;

%mend;

%long

Is there a better solution?

Re: How to TRANSPOSE Orderly?

Kurt_Bremser — Tue, 01 Dec 2020 06:26:52 GMT

Do this

proc sort
  data=have (keep=i)
  out=template
  nodupkey
;
by i;
run;

data pretrans/view=pretrans;
set
  template
  have
;
run;

proc transpose
  data=pretrans
  prefix=x
  out=now (drop=_name_ where=(t ne .))
;
by t;
id i;
run;

(untested, posted from my tablet)

Re: How to TRANSPOSE Orderly?

Junyong — Tue, 01 Dec 2020 07:00:43 GMT

Thanks, but cannot transpose pretrans because unsorted—but can understand the approach (appending template first as nametags).

Re: How to TRANSPOSE Orderly?

ballardw — Tue, 01 Dec 2020 08:24:24 GMT

Do you actually need a data set?

If so, what are you doing that is column order dependent? SAS data sets and procedures really don't care about column order for a vast majority of processes.

If you need a report, that is something people read and get picky about column order maybe:

proc report data=have;
   column t i,x;
   define t /group;
   define i/across;
run;

Re: How to TRANSPOSE Orderly?

Kurt_Bremser — Tue, 01 Dec 2020 08:30:50 GMT

We need to incorporate the sort into the process:

data have;
input i t x;
cards;
1 1 -0.43
1 2 1.45
1 3 0.78
2 3 0.43
2 4 -0.76
2 5 0.08
2 6 0.31
2 7 -1.39
3 2 -1.79
3 3 0.62
3 4 0.66
3 5 -0.96
3 6 0.29
3 7 -2.13
3 8 -0.58
3 9 -2.59
3 10 -0.34
3 11 -0.17
3 12 -0.66
4 1 -2.28
4 2 1.8
4 3 1.45
4 4 -1.19
4 5 1.13
4 6 0.59
4 7 -0.67
5 3 -0.92
5 4 -0.84
5 5 -0.11
5 6 -1.47
;

proc sort
  data=have
;
by t i;
run;

proc sort
  data=have (keep=i)
  out=template
  nodupkey
;
by i;
run;

data pretrans/view=pretrans;
set
  template
  have
;
run;

proc transpose
  data=pretrans
  prefix=x
  out=want (drop=_name_ where=(t ne .))
;
by t;
id i;
run;

Code is now tested.