Transposing and Stacking

[jrdykstr93]

Data-set A:

n	type	x0	x1	groups	track	block0
1	0	var1	value1	1	1	1
1	0	var2	value2	1	2	1
1	0	var3	value3	1	3	1
1	0	var4	value4	1	4	1
1	0	var5	value5	1	5	1
1	0	var6	value6	1	6	1
1	0	var7	value7	1	7	1
2	0	var1	value1	2	1	1
2	0	var2	value2	2	2	1
2	0	var3	value3	2	3	1
2	0	var4	value4	2	4	1
2	0	var5	value5	2	5	1
2	0	var6	value6	2	6	1
2	0	var7	value7	2	7	1
2	0	var4	value4	2	8	2
2	0	var5	value5	2	9	2
2	0	var6	value6	2	10	2
2	0	var7	value7	2	11	2

Data-set B:

n	type	groups	var1	var2	var3	var4	var5	var6	var7
1	0	1	value1	value2	value3	value4	value5	value6	value7
2	0	2	value1	value2	value3	value4	value5	value6	value7
2	0	2				value4	value5	value6	value7

Hello, so basically, I want to take data-set A and turn it into data-set B. I assigned groups, track, and block0 variables to try and help me separate, transpose, and stack this data, which i attempt with the macro below.  This is just a sample, of course. I have block0 that go up to around 120 and groups that go up to 20.  I also have many different types.   I have been using the code below:

%macro separate;
%do i=1 %to 19;
	data d&i.; set x9;
	if groups=&i.;run;

	%let max=;
	data _null_; set d&i.(obs=1);
	call symput('max',groups);run;

	%do t=1 %to &max.;
		data e&t.; set d&i.;
		if block0 = &t.;run;
		
		proc sort data=e&t.; by n type groups;
		proc transpose data=e&t. out=f&t.(drop=_name_); by n type groups; var value; id x0;
		proc append data=f&t. force  base=xxx&i.;
	%end;
proc append data=xxx&i. force base=final;
%end;
%mend;
%separate;

This produces the output I want, for a small subset,  I think.... but I believe it to be inefficient and not robust.  Is there a better way to do this? Please note, that var1-var7 will always be the same and equal, but the frequency of how var4-var7 repeat per n varies, var1-var3 will only occur once per n. Value1-Value7 can take on any values..

[data_null__ · Accepted solution]

proc transpose data=have out=want;
   by n type groups block0;
   var x1;
   id x0;
   run;

[jrdykstr93]

Wow, I couldn't help but laugh after I saw this answer.  Man, ya gotta love when I make things 400 times harder than they need to be! haha. Thanks alot for your help. I feel dumb lol.