BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
yadaw
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

2 REPLIES 2
Shmuel
Garnet | Level 18

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
Super User

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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