how to create new records with variables containing multiple values

[HitmonTran]

Hi All,

I have a variable "Labels" with some having multiple values (which is identified by ",".)

I need to separate these values and create new records along with variable "Total Twitter Impressions".  

eg.

raw data

want data:

thank you all

[PaigeMiller]

So is the logic that you want to implement something like this:

Create a record with text from column A before the comma, and then another record with text from column A after the comma but before the next comma, and so on splitting at every comma, all such records having the same value that was in column B?

[ballardw · Accepted solution]

I am not going to retype anything to create data and can't code from pictures.

This example creates an example data set with pretty trivial data to do similar to what you want.

data have;
   input string :$15. value;
datalines;
a       15
a,b,c   25
b,c,d   0
b,d     5
;

data want;
   set have;
   length newstring $ 10;
   do i= 1 to countw(string,',');
      newstring = scan(string,i,',');
      output;
   end;
   keep newstring value;
run;

Key elements: The length of the Newstring (or what ever you want to name your new variable) needs to be long enough to hold the longest expected value.

The ',' in the COUNTW and SCAN functions is so anything other than a comma is not treated as a boundary between values.

The explicit output statement writes the output for each time through the Do loop, which is counting the number of comma delimited values with Scan selecting the matching value.

The Keep is retain the specific variables. You may or may not want to keep your original variable.

[HitmonTran]

thank you very much! this is awesome