Solved

Separating data in a comma delimited column into individual rows in SAS

Posted on 2016-07-17
1
51 Views
Last Modified: 2016-07-21
Experts,

I have a macro that takes multiple rows and puts them into one comma delimited column:
Example:
Code              Color      
  001               Red
  001               Blue
  001               Green
  001               Yellow
Becomes:

001                   Red, Blue, Green, Yellow

Can anyone tell me how I can take a column that is comma delimited and split the values out to a separate rows:
Code  Lit
 001    Red, Blue, Green, Yellow
Becomes:
Code        Color
001           Red
001           Blue
001          Green
001          Yellow

I would like to do this in SAS
0
Comment
Question by:morinia
1 Comment
 
LVL 8

Accepted Solution

by:
ShannonEE earned 500 total points
ID: 41716314
Hi there  morinia,

Is this homework?

====

I suggest you code this up with a data step.

For each row you just need to read in the column (which you call lit) and break into pieces.

To to that you probably will need a  do - while  loop or a do until loop.

For each piece (inside the loop), assign the piece to a variable called color and then code the statement
output;
to produce an output record for that color.

Useful SAS functions for splitting into pieces could include some of

find()
findw()
index()
indexw()
lengthN()
substr()
translate()


look them up in

base sas function reference   (url is  support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default)/viewer.htm#titlepage.htm)

and decide which of them you will use.  You will probably will need to use at least 2 different functions to look for the comma between items (you may need to be careful about blanks), determine the start, end and length of the piece and finally assign that piece across to the color variable.

Ian
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Where to start on learning SAS ? 5 502
Import excel in SAS 2 842
Assigning a macro variable within a macro in SAS 8 433
Select database in java sas web solution 2 150
Active Directory security has been a hot topic of late, and for good reason. With 90% of the world’s organization using this system to manage access to all parts of their IT infrastructure, knowing how to protect against threats and keep vulnerabil…
Adults who share images on social media aren’t the only ones who need to worry about their privacy. Our culture’s tendency to share every move and celebration affects the privacy of our children, too.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question