Repair input table because some rows concatenate by mistake

[Ronein]

Hello

I have a question please.

I will try to simplify it by a simple example.

There is an external table that I receive.

In this table for example there are 3 fields and should be for example 4 rows.

For example:The data should be like that:

  a   b   c

  d   e   f

  g   h   I

  J   k   L

But I get it like that:

 abc  de  f

 g     h     I

 j      K     L

I want to ask If there is a way to fix the data in a code.

For example: I want to have in each row 3 fields only and each field will contain 1 words(string).

Best solution will be to have a flexible program that can fix data for any number of fields.

thanks

Roms

[LinusH]

Well, anything is possible, especially when it comes to SAS.
But if you don't have a specific (limited) no of cases you wish to handle, chances are that you will be in a never ending loop of fixing new data quality issues, and the result will an unmanageable piece of code.
So, first challenge your source to give you data with a sustainable structure.

[Ronein]

Thanks for your reply.

I gave a specific example.

Do you know how to repair it in sas code?

This is my question.

[Kurt_Bremser]

Make an array with 3 variables.

Retain a counter, initialize to 1.

Do an input without a variable (input line ends up in _infile_)

Compress the _infile_ to remove blanks.

Do for all characters in _infile_:

  Assign the current character to the current array member

  Increment the counter

  If the counter is > 3, reset to 1 and output

When the end of file is reached, output if counter is not 1 (incomplete last observation)

How to put that pseudocode into data step code is left as an exercise for the student 😉

[Patrick]

@Ronein

The challenge starts with your specification.

You define "...and each field will contain 1 words(string)." but then you show us as very first word "abc" but expect this single word to get split up into 3 different variables. What should it be? 1 non-blank character per variable OR words?