topic Re: how to create new records with variables containing multiple values in SAS Programming

how to create new records with variables containing multiple values

HitmonTran — Fri, 18 Sep 2020 16:16:52 GMT

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

Re: how to create new records with variables containing multiple values

PaigeMiller — Fri, 18 Sep 2020 16:51:16 GMT

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?

Re: how to create new records with variables containing multiple values

ballardw — Fri, 18 Sep 2020 17:16:55 GMT

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.

Re: how to create new records with variables containing multiple values

HitmonTran — Fri, 18 Sep 2020 18:47:44 GMT

thank you very much! this is awesome