DATA Step, Macro, Functions and more

Combining two text files

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 138
Accepted Solution

Combining two text files

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.


Accepted Solutions
Solution
‎11-11-2016 09:15 AM
Super User
Posts: 7,763

Re: Combining two text files

Posted in reply to Walternate

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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎11-11-2016 09:15 AM
Super User
Posts: 7,763

Re: Combining two text files

Posted in reply to Walternate

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;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Combining two text files

[ Edited ]
Posted in reply to KurtBremser

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;

 

Super User
Posts: 7,763

Re: Combining two text files

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

infile in dlm=',' truncover;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 5

Re: Combining two text files

Posted in reply to KurtBremser

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


Import.PNG
Super User
Posts: 7,763

Re: Combining two text files

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 377 views
  • 0 likes
  • 3 in conversation