Link to home
Start Free TrialLog in
Avatar of 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       ...

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


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.


Avatar of Matt Kendall
Matt Kendall
Flag of United States of America image

I think what you're trying to do would be easier done in the code after the query result.. Every now and then I do something similiar to this but I go through the initial result you have at the top and put the value that I want to be the 'index' into an array key.

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.
foreach($dbusers as $user){

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['UserID']])>0) $finalarray[$user['UserID']].=" "; //or whatever delimiter you want

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

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Herbalizer


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.: || "/";

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.