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

Hello,

I want to make a combination of 2 files, 1 record of this file and 1 record of other file.

So with 3 records in 1 file and 2 records in other file, I should have 6 combinations.

I write the usual code and suddendy there are few unintended records created in final file (the last 2 record in file want)

The code is so straightforward but I cant find the cause.

Could anyone please have a look and help me fix it?

Many thanks.

HHC

data have_var1; 
input a_name $ a_value b_name $ b_value;
datalines;
a 4 b 5
a 4 c 9
b 5 c 9

;run;


data have_var2; 
input c_name $ c_value;
datalines;
c 90
d 100
;run;

	*Create combination of any 2 row from 2 file;
		data want;									
		set have_var1 nobs=totalobs1;
		drop i ;
		i+1;
		set have_var2 nobs=totalobs2;
		drop k ;
		k+1;

		do j=i to totalobs1 ;
		set have_var1(keep = a_name a_value b_name b_value) point=j;

		do l=k to totalobs2 ;
		set have_var2(keep = c_name c_value) point=l;
		
			/*if a_name^=c_name and b_name^=c_name then*/

			output;
			end;

		end;
		run;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You are making it way too complicated.  

data want;									
do j=1 to totalobs1 ;
  set have_var1(keep = a_name a_value b_name b_value) point=j nobs=totalobs1;
  do l=1 to totalobs2 ;
    set have_var2(keep = c_name c_value) point=l nobs=totalobs2;
    output;
  end;
end;
stop;
run;

Make sure to add a STOP to prevent it from running for ever. Remember that normal data steps stop when they read past the end of the input files. When using POINT= option it will never read past the end of the input.

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

You are making it way too complicated.  

data want;									
do j=1 to totalobs1 ;
  set have_var1(keep = a_name a_value b_name b_value) point=j nobs=totalobs1;
  do l=1 to totalobs2 ;
    set have_var2(keep = c_name c_value) point=l nobs=totalobs2;
    output;
  end;
end;
stop;
run;

Make sure to add a STOP to prevent it from running for ever. Remember that normal data steps stop when they read past the end of the input files. When using POINT= option it will never read past the end of the input.

art297
Opal | Level 21

You can simplify it even more:

 

data want (drop=i);
  set have_var1;
  do i=1 to nobs;
    set have_var2  nobs=nobs point=i;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

hhchenfx
Barite | Level 11
Oh that STOP!
my code really look ... 🙂
Thank you.
HHC
Kurt_Bremser
Super User

Can't try it from where I am at the moment (hotel room in Venice), but try a SQL cartesian join:

proc sql;
create table want as
select *
from have_var1, have_var2
;
quit;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 4 replies
  • 759 views
  • 7 likes
  • 4 in conversation