topic Re: Reading in multiple input lines into one one observation and concatenating in SAS Programming

Reading in multiple input lines into one one observation and concatenating

Sha88 — Wed, 19 Sep 2018 10:18:02 GMT

Hello

I'm having some trouble reading in a file into SAS, it is an ASCII carrot ^ delimited file. The first variable contains a record type and then other data follows, Until you get to A records where there is a second record type followed by data. I need to read in multiple rows for each record type and concatenate them into a single variable within an observation, can this be done? E.g. below, I have already gotten the P, V and R lines into one observation using 'retain' and select while, just don't know how to concat the A^A and A^B into one before inserting into one variable.

Source File e.g.:

P^JOHN^SMITH^26^1000101
V^VISIT 1^01DEC2019
R^RED BLOOD CELLS^10^u/ml
A^A^COMMENTS ABOUT THIS
A^A^TEST THAT APPEAR ON
A^A^DIFFERENT LINES
A^B^MORE COMMENTS ON A
A^B^DIFFERENT LINE THAT
A^B^BELONG IN A DIFFERENT
A^B^COLUMN
R^WHITE BLOOD CELLS^22^u/ml
A^A^COMMENTS ABOUT THIS
A^A^TEST THAT APPEAR ON
A^A^DIFFERENT LINES
A^B^MORE COMMENTS ON A
A^B^DIFFERENT LINE THAT
A^B^BELONG IN A DIFFERENT
A^B^COLUMN

In SAS:

JOHN|SMITH|26|1000101|VISIT 1|01DEC2019|RED BLOOD CELLS|10|u/ml|COMMENTS ABOUT THIS TEST THAT APPEAR ON DIFFERENT LINES|MORE COMMENTS ON A DIFFERENT LINE THAT BELONG IN A DIFFERENT COLUMN|

JOHN|SMITH|26|1000101|VISIT 1|01DEC2019|WHITE BLOOD CELLS|22|u/ml|COMMENTS ABOUT THIS TEST THAT APPEAR ON DIFFERENT LINES|MORE COMMENTS ON A DIFFERENT LINE THAT BELONG IN A DIFFERENT COLUMN|

Ideally looking to loop, during my input statement to concatenate all A^A then A^B and insert these into their own columns on the same row. There can be any number of A_A or A_B records or there can be none.

Thanks 🙂

Re: Reading in multiple input lines into one one observation and concatenating

ChrisNZ — Wed, 19 Sep 2018 23:42:13 GMT

Use the same method (retain and while) and add something like this:

if _INFILE_=:'A^A^' then AA=catx(' ',AA,substr(_INFILE_,5));
if _INFILE_=:'A^B^' then AB=catx(' ',AB,substr(_INFILE_,5));

Post your code if you need more help.

Re: Reading in multiple input lines into one one observation and concatenating

Sha88 — Thu, 20 Sep 2018 08:22:55 GMT

Hi my code below. There are a few other fields in the A record. The code below still outputs the comments into multiple observations.

input @1 RECTYP $ @;

SELECT

.....

/*Read in Alpha Result Records*/
when(RECTYP='A') do;
input
ATXTTYP: $1. ATEXT: $1000. AALPCD: $15. AOTC: $5. ARTC: $5. ARMKCD: $10.
;
if ATXTTYP='A' then ATEXT_ST=catx(' ',ATEXT);
if ATXTTYP='B' then ATEXT_ST_B=catx(' ',ATEXT);
end;
/*Final Step - Otherwise */

.....

otherwise do;

Re: Reading in multiple input lines into one one observation and concatenating

ChrisNZ — Thu, 20 Sep 2018 08:26:31 GMT

You are not using catx correctly.

Re: Reading in multiple input lines into one one observation and concatenating

Sha88 — Thu, 20 Sep 2018 09:07:57 GMT

Figured.