Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2016-07-17
1
Medium Priority
?
96 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 2000 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

Conducting a customer service survey used to be as straightforward as sending a template email out using checkboxes and numerical rating systems to measure satisfaction.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

578 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