Herbalizer

asked on

# SAS-How to transpose all distinct values of a column into separate columns

I have a table containing 2 fields(columns) only. Column1 is UserID and Column2 is Value. Here is an example of the table.

UserID Value

user1 A1-34

user1 B2-99

user1 C5-33

user1 D1-88

user2 A1-34

user3 C5-33

user3 D1-88

. ...

. ...

user99 ...

etc.

What I want is ONLY 1 UserID per row, and I want all the "values" linked to this userID on 1 row.

Wanted Output:

UserID Value1 Value2 Value3 Value4

user1 A1-34 B2-99 C5-33 D1-88

user2 A1-34

user3 C5-33 D1-88

OR EVEN BETTER :

UserID Combined Values

user1 A1-34 B2-99 C5-33 D1-88

user2 A1-34

user3 C5-33 D1-88

I used the "proc transpose" function but it doesn't really meet my needs since the values become columns but then I don't have actual "values" in each columns. Also, the transpose function reformat the values to be valid column names and reformatting each name back to the original value would be really burdensome since I have close to 200 different values. Plus, I don't know how to extract variable names and put em into values. Either way, the reformatting is useless and I am convinced there is a more direct way to to do that.

Thanks.

UserID Value

user1 A1-34

user1 B2-99

user1 C5-33

user1 D1-88

user2 A1-34

user3 C5-33

user3 D1-88

. ...

. ...

user99 ...

etc.

What I want is ONLY 1 UserID per row, and I want all the "values" linked to this userID on 1 row.

Wanted Output:

UserID Value1 Value2 Value3 Value4

user1 A1-34 B2-99 C5-33 D1-88

user2 A1-34

user3 C5-33 D1-88

OR EVEN BETTER :

UserID Combined Values

user1 A1-34 B2-99 C5-33 D1-88

user2 A1-34

user3 C5-33 D1-88

I used the "proc transpose" function but it doesn't really meet my needs since the values become columns but then I don't have actual "values" in each columns. Also, the transpose function reformat the values to be valid column names and reformatting each name back to the original value would be really burdensome since I have close to 200 different values. Plus, I don't know how to extract variable names and put em into values. Either way, the reformatting is useless and I am convinced there is a more direct way to to do that.

Thanks.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Excellent ! I knew it was simple. Just couldn't find a way to express my needs through the SAS help index.

correction to solution : "lengh.." should be "format valuelist $char32000.;"

Also, the combination of "Trim" and the || " "; at the end of the line doesn't produce good results as the trim function removes the " " in between values. Hence, another separator should be used at the end of the line. ex.: || "/";

Thanks

PS: sorry Kendaltech, I'm pretty sure your explanation was right, but I really needed the SAS code. Thanks both of you for your help.

correction to solution : "lengh.." should be "format valuelist $char32000.;"

Also, the combination of "Trim" and the || " "; at the end of the line doesn't produce good results as the trim function removes the " " in between values. Hence, another separator should be used at the end of the line. ex.: || "/";

Thanks

PS: sorry Kendaltech, I'm pretty sure your explanation was right, but I really needed the SAS code. Thanks both of you for your help.

I dont know what language you're using along with the database but this is how I'd do it in php. Lets say you get that top result as an array called $dbusers.

$finalarray=Array();

foreach($dbusers as $user){

$finalarray[$user['UserID'

}

Then you have an array like your wanted output. You could make it like your even better output by changing the array push to an add on string by changing the code inside the foreach to this:

if (strlen($finalarray[$user[

$finalarray[$user['UserID'

Then you'd get an array of the userID's as the key and the value would be a string of the combined values.