Help using Base SAS procedures

How to process two SAS data sets simultaneously.

Reply
Occasional Contributor
Posts: 5

How to process two SAS data sets simultaneously.

I have two SAS data sets A and B. Data set A has about 20000 rows and data set B has about 30000 rows. I want to process each row in data set A against all rows in data set B. I know this can be done using Merge statement but the cartesian product results in 600 million rows in the final data set. This is blowing up my memory. Is there any way of doing this in a single data step without using the merge statement. I pretty sure there is one, but couldn't remember what I did several years ago.
PROC Star
Posts: 7,467

Re: How to process two SAS data sets simultaneously.

You could use something like:
[pre]
data have;
input x y;
cards;
1 2
3 4
;
data want;
set have;
do i=1 to n;
set sashelp.class nobs=n point=i;
output;
end;
run;
[/pre]
HTH,
Art
---------
> I have two SAS data sets A and B. Data set A has
> about 20000 rows and data set B has about 30000 rows.
> I want to process each row in data set A against all
> rows in data set B. I know this can be done using
> Merge statement but the cartesian product results in
> 600 million rows in the final data set. This is
> blowing up my memory. Is there any way of doing this
> in a single data step without using the merge
> statement. I pretty sure there is one, but couldn't
> remember what I did several years ago. Modified to correct code that was posted prematurely and without any testing. Oops!


Message was edited by: art297
Occasional Contributor
Posts: 5

Re: How to process two SAS data sets simultaneously.

Thanks for your help. Looks like this code processes only for the first row of Data set A.
PROC Star
Posts: 7,467

Re: How to process two SAS data sets simultaneously.

Did you run the code? It should have given you 19 records for each record in the first file (i.e., all of the records from the first file combined with all of the records from the second file.

Art
----------
> Thanks for your help. Looks like this code processes
> only for the first row of Data set A.
Occasional Contributor
Posts: 5

Re: How to process two SAS data sets simultaneously.

I ran the code. It gave me 19 records for the first records in the first file.
Regular Contributor
Posts: 171

Re: How to process two SAS data sets simultaneously.

The eof marker on the sashelp.class dataset is being reached before the second observation in the have dataset can be processed, causing the data step to terminate prematurely. You could try the following code instead, but I don’t see how this would be any more efficient than the SQL Cartesian product. [pre]
data have;
input x y;
cards;
1 2
3 4
;
data want;
set have;
do i=1 to n;
set sashelp.class nobs=n point=i;
output;
end;
run;
[/pre]
Occasional Contributor
Posts: 5

Re: How to process two SAS data sets simultaneously.

Thanks for your help. This works on the example data set. I am running it on my large data set. Hope it runs.
Super Contributor
Super Contributor
Posts: 3,174

Re: How to process two SAS data sets simultaneously.

From the OP: " I want to process each row in data set A against all rows in data set B"

How about explaining in more detail what you are trying to accomplish, specifically listing logic and/or SAS variables used, for what purpose, input/output, and the like.

There are many SAS features, some of which have been mentioned which may be more suitable.

Also, share what SAS code you have attempted for feedback thus far -- that will also help you get constructive and useful feedback and guidance.

Scott Barry
SBBWorks, Inc.
PROC Star
Posts: 7,467

Re: How to process two SAS data sets simultaneously.

Sorry. The code I placed was, unfortunately, not tested. Fortunately, you now have the correct code.

Art
Regular Contributor
Posts: 171

Re: How to process two SAS data sets simultaneously.

If you have SAS 9.2, the data step hash object can sometimes be used to perform “SQL-like joins.” Here is a good paper on the topic.

http://support.sas.com/rnd/base/datastep/dot/better-hashing-sas92.pdf
Occasional Contributor
Posts: 5

Re: How to process two SAS data sets simultaneously.

Thank you.
Ask a Question
Discussion stats
  • 10 replies
  • 168 views
  • 0 likes
  • 4 in conversation