topic Re: Select a few distinct variable values from a list in SAS Programming

Select a few distinct variable values from a list

Wickedestjr — Tue, 20 Feb 2024 17:09:08 GMT

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!

Re: Select a few distinct variable values from a list

FreelanceReinh — Tue, 20 Feb 2024 18:44:44 GMT

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;

Re: Select a few distinct variable values from a list

SASJedi — Tue, 20 Feb 2024 18:53:11 GMT

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;