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!
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;
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;
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.
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.
Ready to level-up your skills? Choose your own adventure.