topic How to combine two tables by using MIN and MAX numbers? in SAS Programming

How to combine two tables by using MIN and MAX numbers?

ybz12003 — Wed, 24 Apr 2024 16:34:08 GMT

Hello,

I would like to combine two datasets, HAVE and Weekcode. I would like to use MIN and MAX numbers in the weekcode column to set the "WANT" dataset range. Please help.

data HAVE;  
	Format Weekcode 4.0 Test 3.0 Pos 3.0 Lab 4.0; 
	infile datalines delimiter='/'; 
	input Weekcode Test Pos Lab;  
	datalines;                     
2102/ 8/ 3/ 256/
2103/ 3/ 1/ 619/
2106/ 5/ 2/ 2066/
2107/ 3/ 0/ 256/
2110/ 7/ 6/ 619/
;  

data weekcode;  
	Format Weekcode 4.0; 
	infile datalines delimiter='/'; 
	input Weekcode;  
	datalines;                     
2009/
2100/
2101/ 
2102/
2103/ 
2104/
2105/ 
2106/ 
2107/ 
2108/ 
2109/ 
2110/ 
2111/ 
2112/ 
2113/ 
2114/ 
2115/ 
;  

data WANT;  
	Format Weekcode 4.0 Test 3.0 Pos 3.0 Lab 4.0; 
	infile datalines delimiter='/'; 
	input Weekcode Test Pos Lab;  
	datalines;                     
2102/ 8/ 3/ 256/
2103/ 3/ 1/ 619/
2104/ 0/ 0/ 0/
2105/ 0/ 0/ 0/
2106/ 5/ 2/ 2066/
2107/ 3/ 0/ 256/
2108/ 0/ 0/ 0/
2109/ 0/ 0/ 0/
2110/ 7/ 6/ 619/
;

Re: How to combine two tables by using MIN and MAX numbers?

ballardw — Wed, 24 Apr 2024 16:55:03 GMT

Since Weekcode appears in both your Have and Weekcode data sets which dataset is to be used to generate the Min and Max you are thinking of?

I might guess :

proc sql noprint;
   select min(weekcode), max(weekcode) into :minweek, :maxweek
   from have
   ;
quit;

data want; 
  merge have weekcode (where=(&minweek. le weekcode le &maxweek.));
  by weekcode;
run;

(Trivial to add the 0 instead of missing if actually desired. I'm cautious about doing such though.)

Re: How to combine two tables by using MIN and MAX numbers?

ybz12003 — Wed, 24 Apr 2024 17:10:58 GMT

That works!