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.
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.
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.