Unix command with SAS

[yadaw]

Hi Guys...

I have two files with some data like:-

file1.txt

111111111111111

222222222222222

333333333333333

444444444444444

555555555555555

666666666666666

file2.txt

999999999999999

333333333333333

111111111111111

666666666666666

888887756643656

343546567576888

232343454634546

897600000000000

444444444444444

Now my question is that I want only those data at output witch is common in both the file.

Like:-

333333333333333

111111111111111

666666666666666

444444444444444

In order to do this:-

DATA file1;

INFILE ‘mypath/file1.txt’

INPUT part1 $;

RUN;

DATA file2;

INFILE ‘mypath/file2.txt’

INPUT part2 $;

RUN;

PROC sql;

SELECT * FROM file1 INTERSECT SELECT * FROM file2;

quit;

The output of my program is like:-

SAS output system

name

------------------------

333333333333333

111111111111111

666666666666666

444444444444444

But i want only

333333333333333

111111111111111

666666666666666

444444444444444

as output.

Then again i modified by adding one new line:-

X ‘sh /shell_script/cut.sh’; /* absolute path */

And cut.sh is:-

tail -n +6 outputFile.lst > outFile.txt

but outFile.txt is not created.

What is wrong in my program?

Note:- I am using SAS grid server on putty.

[Shmuel]

Did you mean:

DATA file1;
INPUT text $;
cards;
111111111111111
222222222222222
333333333333333
444444444444444
555555555555555
666666666666666
;
RUN;

DATA file2;
INPUT text $;
cards;
999999999999999
333333333333333
111111111111111
666666666666666
888887756643656
343546567576888
232343454634546
897600000000000
444444444444444
;
RUN;

proc sql;
create table want as
select * from file1 as A
inner join file2 as B
on A.text = B.text;
quit;

[Kurt_Bremser · Accepted solution]

Do not use a SQL select to write raw data to an external file. Instead create a dataset, and write the output file directly from that with a data step:

PROC sql;
create table want as
SELECT * FROM file1 INTERSECT SELECT * FROM file2;
quit;

data _null_;
file "outFile.txt";
set want;
put part1;
run;

Add output path etc to the file statement.

This way you do not need to manipulate the SAS output into the desired form from the commandline.

Additional hint: whenever you want to use an external command, do not rely on the rather crude X statement. Instead do this:

filename oscmd pipe "your command 2>&1";

data _null_;
infile oscmd;
input;
put _infile_;
run;

filename oscmd clear;

All output from the command (including error messages, that's the function of the 2>&1) will end up in the SAS log for inspection/debugging.