DATA Step, Macro, Functions and more

Unix command with SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Unix command with SAS

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.


Accepted Solutions
Solution
‎02-13-2018 12:23 AM
Super User
Posts: 10,570

Re: Unix command with SAS

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Trusted Advisor
Posts: 1,848

Re: Unix command with SAS

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;
Solution
‎02-13-2018 12:23 AM
Super User
Posts: 10,570

Re: Unix command with SAS

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 116 views
  • 1 like
  • 3 in conversation