Solved

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

Posted on 2016-07-17
1
29 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

We have come a long way with backup and data protection — from backing up to floppies, external drives, CDs, Blu-ray, flash drives, SSD drives, and now to the cloud.
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now