Split column into rows grouped by 2 other columns

Accepted Solution Solved
Reply
Regular Contributor
Posts: 164
Accepted Solution

Split column into rows grouped by 2 other columns

Hi

I have a table that looks like the following

 

User Date Code
User1 5/1/15  A918, A221, Y838, Y9222
User1 6/1/15  A221, A922, T179, 
User2 7/1/15  R104, J182, Z720
User3 7/1/15  A831, N179, E1129, N185
User4 8/1/15  A500
User4 9/1/15  R104, A2970, E1172

 

I would like to split the column "Code" by the deliminator ',' to the follwing

User Date Code
User1 5/1/15  A918
User1 5/1/15  A221
User1 5/1/15 Y838
User1 5/1/15 Y9225
User1 6/1/15  A221
User1 6/1/15 A922
User1 6/1/15 T179
User2 7/1/15  R104
User2 7/1/15 J182
User2 7/1/15 Z722
User3 7/1/15  A831
User3 7/1/15 N179
User3 7/1/15 E1129
User3 7/1/15 N188
User4 8/1/15  A500
User4 9/1/15  R104
User4 9/1/15 A2970
User4 9/1/15 E1174

 

In other words, the string i the column (Code) will need to be split by the deliminator and grouped by the coulmns (User) and (Date)

Any suggession is appreciated

Kind regards


Accepted Solutions
Solution
‎06-28-2017 08:51 AM
Regular Contributor
Posts: 164

Re: Split column into rows grouped by 2 other columns

Sorry, found the solulsion :

 

 

  data NewSet;

   set OldSet;

   Old= Code;

   do i= 1 to (countw(Old));

      New = scan(Old, i,',');

      output;

   end;

   drop i Old Code;

run;

 

 

View solution in original post


All Replies
Solution
‎06-28-2017 08:51 AM
Regular Contributor
Posts: 164

Re: Split column into rows grouped by 2 other columns

Sorry, found the solulsion :

 

 

  data NewSet;

   set OldSet;

   Old= Code;

   do i= 1 to (countw(Old));

      New = scan(Old, i,',');

      output;

   end;

   drop i Old Code;

run;

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 92 views
  • 0 likes
  • 1 in conversation