Solved

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

Posted on 2016-07-17
1
75 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

Successful collaboration among team members is essential for the growth of your business. When employees work together on projects, share ideas and communicate effectively they get better results.
This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

624 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