Combining two text files

[Walternate]

Hi,

I have two text files. Each of them has a continuous numeric variable (the same variable across both, ContVar); one of them also has a categorical var. Basically, I want to create a format in which all values of ContVar in either text file are valid values for Var1 in my SAS dataset.

Textfile 1

ContVar   CategVar

12345         abc

23456         def

34567         ghi

etc.

Textfile 2

Contvar

890123

874321

etc.

I know the simple but inefficient way to do this (1. Create SAS dataset from textfile 1, 2. Create SAS dataset from textfile2, 3. Stack them and then create the format file). I'm hoping there is a more efficient way.

When I thought I only needed the values from Textfile 1, this is the formatting step I came up with:

data want (keep=start label hlo fmtname);

infile "path/Textfile1.txt" end=eof;

input start CategVar $;

retain fmtname '$fmt_name';

retain label 'VALID';

output;

if eof;

start=' ';

label='NOT VALID';

hlo='O';

output;

run;

proc format cntlin=want;run;

I'm hoping Textfile 2 can just somehow be incorporated into that (or similar) rather than the process I outlined above.

Any help is much appreciated.

[Kurt_Bremser · Accepted solution]

You can combine multiple filenames in a filename statement:

filename in ('$HOME/test1.txt','$HOME/test2.txt');
data test;
infile in truncover;
input x1 $ x2 $;
run;

[tgalliga]

Hi All,

I tried this solution and it didn't work. I've attached the 2 text files that I used.

Book 1 has 2 columns (x1 and y) and Book 2 and 1 column (x2)

Data gets stacked but all X1 and Y are combined

filename in ('C:\stageAreasSas\textImport\Book1.txt','C:\stageAreasSas\textImport\Book2.txt');

data test;

infile in truncover;

input x1 $ x2 $;

run;

[Kurt_Bremser]

Since Book1.txt is delimited with a comma (and Book2.txt only has one column), use

infile in dlm=',' truncover;

[tgalliga]

Thanks,that works.

I tried it with tab separated initially but that didn't work so I when to csv. now working wiht the dlm.

Quick question.

the column names are imported as rows. do you know how to tell the import that they are column headers

Thanks,

T

[Kurt_Bremser]

Using them as column headers makes no sense, as you can't change your dataset structure halfway through the data step.

Instead you define the columns with the input statement, and you best simply discard those lines:

data test;
infile in truncover;
input x1 $ x2 $;
if upcase(substr(x1,1,1)) ne 'X';
run;

If you only had to read one file, using firstobs=2 in the infile statement will also do the trick.