For Var 1 break Var2 by all data

[leogaller]

Hi guys!

I am very new in SAS Base programming, I would like of ideas about the following situation:

I am importing an excel spreadsheet, and on cell 1 I have the ID and on cell 2 multiple values separated by ','.

What I need is to repeat the value for cell 1 for all the values of cell 2 in different lines.

See the example below:

What I have

What I need

Any ideas?

Thanks in advance!

LeoGaller

[ballardw]

Have already brought the data into SAS? That may sound silly but we have such things sometimes.

Some additional things to consider:

   Do you need the original variable with multiple values when done?

   Are there any other variables to consider?

   What to do if the column 2 variable is blank? Should there be any output (remove the record) or have the line basically output as is?

And it is difficult to read the picture so an example data, preferably in the form of a datast would help for us to have something to actually work with. This link has https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... instructions on how to turn a dataset into data step code.

BTW, the image of your example data seems to show a period between the values on the third row. Can that possibly occur?.

[leogaller]

Hi Ballardw!

Have already brought the data into SAS?

Yes, the data is already in SAS.

Do you need the original variable with multiple values when done?

Yes, I need, because later I will join the result dataset with another one.

Are there any other variables to consider?

Not now. I just need these two right now.

What to do if the column 2 variable is blank?

There is no blank values in variable 2 because I treated that situation when I was importing the data into SAS.

Should there be any output (remove the record) or have the line basically output as is?

Yes, I need an output just with the 2 variables.

BTW, the image of your example data seems to show a period between the values on the third row. Can that possibly occur?

Actually that is a comma, I want to break the data using that comma.

For example, write the value o var1, if var2 has two values separated by comma, I want to repeat var1 for each value in var2 that are separated.

Thanks for your attention and support! 🙂

Here is the code for sample data:

data sample_data;
	input number $ PreExecution $20.;
	datalines;
	5044 5297,5367
	5055 5297,5367,5467
;

LeoGaller

[ballardw · Accepted solution]

Here is an example with a small dataset.

The example will have an issue if the col2 variable is blank, which is why I asked what you want to do in that case.

This assumes comma is the only delimiter between the values that will be encountered, it drops the original value for column 2 and assumes that the longest of the values within column 2 is 10 characters.

data have;
   informat id best4. col2 $40.;
   input id col2;
datalines;
111   0
112   0
123   5401,5402
124   5401,5402,5403,5404,5406
;
run;

data want;
   set have;
   length newcol2 $ 10.;
   do i= 1 to (countw(col2));
      newcol2 = scan(col2,i);
      output;
   end;
   drop i col2;
run;

[leogaller]

Many thanks Ballardw, your explanation was perfect! \o/