topic Repair input table because some rows concatenate by mistake in SAS Programming

Repair input table because some rows concatenate by mistake

Ronein — Thu, 10 May 2018 07:18:41 GMT

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

Re: Repair input table because some rows concatenate by mistake

LinusH — Thu, 10 May 2018 07:26:21 GMT

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.

Re: Repair input table because some rows concatenate by mistake

Ronein — Thu, 10 May 2018 07:55:42 GMT

Thanks for your reply.

I gave a specific example.

Do you know how to repair it in sas code?

This is my question.

Re: Repair input table because some rows concatenate by mistake

Kurt_Bremser — Thu, 10 May 2018 09:37:50 GMT

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 😉

Re: Repair input table because some rows concatenate by mistake

Patrick — Thu, 10 May 2018 10:16:57 GMT

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