EG: Create new column based on duplicate values in a different column

[Eric_MIG]

All

My SAS EG v7.15 output data of (characters only) has two rows are identical with identical information. All except one column which will always have different character values. My problem is that it may not be just two rows of identical information, it could be three, four, a hundred, etc. Irrespective of how many duplicate rows of information, that one column will always have a unique value. I have an example below of my current output. As you can see, Column A has "V" three times and Column B has "W" three times, but Column C has a different value for each row. I'd like to bring the values of Column C up to one master row and create new columns for the different Column C values as in the example of what i would like the output to look like.

Is there a program or a function in an expression which can determine if two values in one column are equal to each other, and if so, to take a different column value and make its own column within one row of data?

My output now:

	Column A	Column B	Column C
Row 1	V	W	X
Row 2	V	W	Y
Row 3	V	W	Z
Row 4	D	E	F
Row 5	G	H	J
Row 6	G	H	K

What I want the output to look like:

	Column A	Column B	Column C	Column D	Column E
Row 1	V	W	X	Y	Z
Row 2	D	E	F
Row 3	G	H	J	K

Any guidance or help to get me started is greatly appreciated.

Thank you,

Eric

[Reeza · Accepted solution]

If you mean define grouping variables as A and B, no. But if you can tell SAS that A & B are you grouping proc transpose will restructure your data for you. So, if you know that all variables except one will be different it's relatively easy to flip the data set. Use the list of common variables as your BY statement, the variable name to be flipped as ID and the value to be in the table as the VAR value.

If you can't figure it out, post the code and we can help you fix it.

[Eric_MIG]

Thank you Reeza! I'll send out a reply after I try the Proc Transpose.

[Eric_MIG]

It did work with a little variations. The output literally named the new columns "COL1", "COL2" etc. I am not sure how to customize the names of those columns yet. Program also took my 'var' column name and copied it all the way down, but that is no problem to remove in a later step. Here is what I found:

proc transpose data=WORK.QueryOutput out=work.QueryOutputtransposed;
by Column A Column B;
var Column C;
run;

My output then (which worked for me):

	Column A	Column B	Column C	COL1	COL2	COL3
Row 1	V	W	Column C	X	Y	Z
Row 2	D	E	Column C	F
Row 3	G	H	Column C	J	K

Thank you again Reeza!

[Reeza]

PREFIX on the PROC TRANSPOSe statement to control the variable prefix but it will still number PREFIX1, PREFIX2 etc.

If you have another variable that should be the variable name, that goes into your ID statement.