topic Re: Combining two text files in SAS Programming

Combining two text files

Walternate — Fri, 11 Nov 2016 14:10:12 GMT

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.

Re: Combining two text files

Kurt_Bremser — Fri, 11 Nov 2016 14:14:59 GMT

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;

Re: Combining two text files

tgalliga — Tue, 15 Nov 2016 09:22:26 GMT

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;

Re: Combining two text files

Kurt_Bremser — Tue, 15 Nov 2016 09:56:46 GMT

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

infile in dlm=',' truncover;

Re: Combining two text files

tgalliga — Tue, 15 Nov 2016 10:05:39 GMT

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,

Re: Combining two text files

Kurt_Bremser — Tue, 15 Nov 2016 10:22:44 GMT

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.