Adding Additional Rows in Data Set

[shubham_d]

data haveTableOne;
input id code1 $ code2 $ code3 $;
datalines;
1 ABC EFG XYZ
2 ABC . .
3 . ABC XYZ
;

run;

ID	Code 1	Code 2	Code 3
1	ABC	EFG	XYZ
2	ABC	-	-
3	-	ABC	XYZ

wantTableOne, here I want the data in this manner

ID	Overall   Codes
1	ABC
1	EFG
1	XYZ
2	ABC
3	ABC
3	XYZ

Here, I have a different dataset where I want to merge the above code

data haveTableTwo;
input id var1 $ var2 $ var3 $;
datalines;
1 A B C
2 X Y Z
3 X Y Z
;
run;

id	var1	var2	var3
1	A	B	C
2	E	F	G
3	X	Y	Z

data wantTableTwo, here the id, var1-var3 would repeat as per the no. of codes present

ID	var1	var2	var3	Overall_codes
1	A	B	C	ABC
1	A	B	C	EFG
1	A	B	C	XYZ
2	E	F	G	ABC
3	X	Y	Z	ABC
3	X	Y	Z	XYZ

It's okay to not have wantTableOne & directly get the wantTableTwo. 

I hope the question is clear, any kind of help is appreciated, thanks! 

[Quentin]

Can you show the code you have tried?

Are you familiar with PROC TRANSPOSE? Arrays? The MERGE statement? PROC SQL?

[shubham_d]

New to SAS but I've used Arrays & Merge statement. Once I get the wantTableOne, I can use the output statement & merge it onto table two. Still thinking of how I can create wantTableOne

[ballardw · Accepted solution]

One way:

data haveTableOne;
input id code1 $ code2 $ code3 $;
datalines;
1 ABC EFG XYZ
2 ABC . .
3 . ABC XYZ
;

run;

proc transpose data=havetableone 
   out=wanttable1 (drop=_name_ rename=(col1=OverallCode) where=( not missing(overallcode)));
  by id;
  var code1-code3;
run;

data haveTableTwo;
input id var1 $ var2 $ var3 $;
datalines;
1 A B C
2 X Y Z
3 X Y Z
;

data wanttabletwo;
   merge havetabletwo
         wanttable1
   ;
   by id;
run;

Warnings: If you have multiple rows with the same ID in the Havetableone this may not work.

Also the BY statements in the Proc Transpose and the last data step are going to expect the data to be sorted by ID.

[shubham_d]

Hey! Thanks, this was an easy-to-understand solution.

[MayurJadhav]

You can use array to combine data from all the character variables and put it under one variable and then perform the merge on haveTableTwo & wantTableOne datasets. 

data haveTableOne;
	input id code1 $ code2 $ code3 $;
	datalines;
1 ABC EFG XYZ
2 ABC . .
3 . ABC XYZ
;
run;

/* combine data from character variable into one variable */
data wantTableOne (where=(Overall_codes ne ''));
	set haveTableOne;
	array cvars {*} _character_;

	do i=1 to dim(cvars);
		Overall_codes=cvars{i};
		output;
	end;
	drop i code1-code3;
run;

data haveTableTwo;
	input id var1 $ var2 $ var3 $;
	datalines;
1 A B C
2 X Y Z
3 X Y Z
;
run;

data wantTableTwo;
	merge haveTableTwo wantTableOne;
	by id;
run;

Output:

[shubham_d]

Hey! Thanks, this worked for me