BookmarkSubscribeRSS Feed
Wickedestjr
Fluorite | Level 6

Hello,

 

I currently have code that looks something like this:

 

proc sql; create table [Table 3] as select *

from [Table 2]

where [VARIABLE] in ( select distinct [VARIABLE] from [Table 1] );

quit;

 

This code works as intended. However, what I'm now trying to do is figure out if there's a way to select a limited number of values for the [VARIABLE] from [Table 1]. For example, selecting only the first three different values of [VARIABLE] from [Table 1]. Is there a simple way that I can modify my code to accomplish this?

 

Thanks!

2 REPLIES 2
FreelanceReinh
Jade | Level 19

Hello @Wickedestjr,

 

I would probably select that "limited number of values" in a preliminary step, store them in a macro variable (if feasible) and replace the subquery with a macro variable reference.

 

Example using the first three different values of variable MAKE from SASHELP.CARS:

%let table1=sashelp.cars;
%let variable=make;
%let nval=3;

data _null_;
array _t[&nval] $999 _temporary_;
set &table1;
if &variable ~in _t then do;
  _c+1;
  _t[_c]=&variable;
end;
length _v $32767;
if _c=&nval then do;
  do _c=1 to _c;
    _v=catx(' ',_v,quote(trim(_t[_c])));
  end;
  call symputx('values',_v);
  stop;
end;
run;

%put &=values;

proc sql;
create table table3 as select *
from sashelp.cars
where &variable in (&values);
quit;

 

SASJedi
SAS Super FREQ

This is pretty easy to accomplish if you are willing to use PROC FedSQL instead of PROC SQL. FedSQL supports the LIMIT clause

/* Create the sample data */
data table1 table2;
	ID+1;
	do numericValue=1 to 9;
		output Table2;
		if mod(numericValue,3)=0 then do;
			output Table1;
			output Table1;
		end;
	end;
run;

/* Do the work */
proc FedSQL; 
create table Table3 as 
	select *
		from Table2
	where numericValue in 
		(select distinct numericValue 
                     from Table1 
                              limit 2)
;
quit;
Check out my Jedi SAS Tricks for SAS Users

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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