topic Re: Adding Additional Rows in Data Set in SAS Enterprise Guide

Adding Additional Rows in Data Set

shubham_d — Mon, 13 Feb 2023 17:58:38 GMT

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!

Re: Adding Additional Rows in Data Set

Quentin — Mon, 13 Feb 2023 18:21:09 GMT

Can you show the code you have tried?

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

Re: Adding Additional Rows in Data Set

ballardw — Mon, 13 Feb 2023 18:27:12 GMT

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.

Re: Adding Additional Rows in Data Set

shubham_d — Mon, 13 Feb 2023 20:58:17 GMT

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

Re: Adding Additional Rows in Data Set

MayurJadhav — Mon, 13 Feb 2023 23:04:21 GMT

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:

Re: Adding Additional Rows in Data Set

shubham_d — Fri, 17 Feb 2023 09:16:46 GMT

Hey! Thanks, this worked for me

Re: Adding Additional Rows in Data Set

shubham_d — Fri, 17 Feb 2023 09:22:44 GMT

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