DATA Step, Macro, Functions and more

For Var 1 break Var2 by all data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

For Var 1 break Var2 by all data

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

Exemplo.PNG

 

What I need

Exemplo2.PNG

 

Any ideas?

 

Thanks in advance!

 

LeoGaller


Accepted Solutions
Solution
‎07-28-2016 03:43 PM
Super User
Posts: 10,500

Re: For Var 1 break Var2 by all data

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;

View solution in original post


All Replies
Super User
Posts: 10,500

Re: For Var 1 break Var2 by all data

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?.

  

Occasional Contributor
Posts: 18

Re: For Var 1 break Var2 by all data

[ Edited ]

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! Smiley Happy

 

Here is the code for sample data:

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

LeoGaller

 

Solution
‎07-28-2016 03:43 PM
Super User
Posts: 10,500

Re: For Var 1 break Var2 by all data

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;
Occasional Contributor
Posts: 18

Re: For Var 1 break Var2 by all data

Many thanks Ballardw, your explanation was perfect! \o/
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 269 views
  • 1 like
  • 2 in conversation