BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jrdykstr93
Obsidian | Level 7

Data-set A:

ntypex0x1groupstrackblock0
10var1value1111
10var2value2121
10var3value3131
10var4value4141
10var5value5151
10var6value6161
10var7value7171
20var1value1211
20var2value2221
20var3value3231
20var4value4241
20var5value5251
20var6value6261
20var7value7271
20var4value4282
20var5value5292
20var6value62102
20var7value72112


Data-set B:

ntypegroupsvar1var2var3var4var5var6var7
101value1value2value3value4value5value6value7
202value1value2value3value4value5value6value7
202   value4value5value6value7


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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19
proc transpose data=have out=want;
   by n type groups block0;
   var x1;
   id x0;
   run;

View solution in original post

2 REPLIES 2
data_null__
Jade | Level 19
proc transpose data=have out=want;
   by n type groups block0;
   var x1;
   id x0;
   run;
jrdykstr93
Obsidian | Level 7

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.

 

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1218 views
  • 3 likes
  • 2 in conversation