BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
leogaller
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

4 REPLIES 4
ballardw
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7
Many thanks Ballardw, your explanation was perfect! \o/

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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