topic Re: Creating columns by extracting string from a Row in SAS Programming

Creating columns by extracting string from a Row

Angel24 — Mon, 30 May 2022 04:39:33 GMT

Hi All,

I need your help generating variables in SAS. Below is an example of the data which I have in a txt format:

Random Data
Random Data
Random Data
Include... ABC
Include... DEF
Include... GHI
Include... JKL

123456789123
ABC ".." [..] 12 34
DEF ".." [..] 56 37
JKL ".." [..] 64 73
23455122467
ABC ".." [..] 67 43
GHI ".." [..] 12 46
JKL ".." [..] 87 56
XYZ ".." [..] 45 78
56745123446
ABC ".." [..] 78 45
DEF ".." [..] 12 54
STU ".." [..] 32 82
LMN ".." [..] 45 36

To be considered:
XYZ
STU
LMN

Random Data

I want the output in the below format:

Column1	ABC_1	ABC_2	DEF_1	DEF_2	GHI_1	GHI_2	JKL_1	JKL_2	XYZ_1	XYZ_2	STU_1	STU_2	LMN_1	LMN_2
123456789123	12	34	56	37			64	73
23455122467	67	43			12	46	87	56	45	78
5674512344	78	45	12	54							32	82	45	36

Basically, I want to extract the strings after the word "include" and also the strings below "To be considered:" from the txt file and make them column headers followed by _1 and _2 and allocate the values accordingly as shown above in the output.

Any help will be greatly appreciated. Thanks!

Re: Creating columns by extracting string from a Row

Oligolas — Tue, 31 May 2022 12:05:13 GMT

Hi,

I'd try first to import the *.txt file into a dataset to see if I could use the data structure to create the output I want

If this is not feasible I think I'd import it as bulk in a first step, use regular expressions or text analysis function (index, findw) in a second step to extract the data I need and finally create the output in a third step.

Since this is mainly data triggered you need to know what the data exactly looks like and if the data structure may change in future (maxim 3)

Re: Creating columns by extracting string from a Row

PaigeMiller — Tue, 31 May 2022 12:18:59 GMT

What is the reason why you want the output in this specific format? Are you going to be producing a report that looks like what you have shown? Or do you need a SAS data set that looks like what you have shown for additional analyses? (If so, what additional analyses?)