SAS Macro - Array

Question

%macro nextNB(ds);
%local dsid nv rc;
%let dsid = %sysfunc(open(&ds));
%let nv = %sysfunc(smallest(2, &dsid));
%let rc =%sysfunc(close(&dsid));
&nv
%mend nextNB;

%put %nextNB(WORK.TEST);

&dsid returns a dataset with values 5, 7 and 9. How do I change my macro function such that it returns the 2nd smallest value 7?

What are you trying to do? If you already have the data in a dataset why are trying to analyze it using macro code? — Tom, Mar 08 '16 at 14:13
Because eventually, I'm going to replace the data-set with a query inside this macro. — Jayesh Surendran Menon, Mar 08 '16 at 16:10
If you replace the dataset with a query then the macro will need to generate SAS code. So you could no longer create a function style macro that returns just a value. — Tom, Mar 08 '16 at 16:24
@Tom - Could you please help me recreate my code then. I want to be able to get the values 5, 6, 7 from a query and then use a macro function to choose the kth value and use that macro in an EG query builder. — Jayesh Surendran Menon, Mar 08 '16 at 17:36
I think you may want a function instead of a macro. Have you looked into PROC FCMP. Also, why can't you use the largest/smallest functions within the EG query builder? — Reeza, Mar 09 '16 at 22:41

Tom · Answer 1 · 2016-03-09T20:57:20.993

1

If you just want to use an existing dataset and place the Kth value into a macro variable then just use a data step.

Let's mimic passing in your dataset name, variable name, an integer number and target macro variable name as macro variables.

 %let ds=list ;
 %let var=value ;
 %let k=2 ;
 %let target=new_mv ;

Then your data step is just.

 data _null_;
   set &list firstobs=&k obs=&k ;
   call symputx("&target",&var);
 run;

If it isn't sorted then perhaps you can just sort first?

 proc sort data=&ds nodupkey ;
   by &var;
 run;

edited Mar 09 '16 at 20:57

answered Mar 08 '16 at 17:57

Tom

47,574
2
16
29

I really like this suggestion and going to try by the end of the week and will let you know. Thank you so much for taking the time. – Jayesh Surendran Menon Mar 09 '16 at 18:24

score 0 · Answer 2 · answered Mar 08 '16 at 17:39

To do this as a macro function you will need to read the whole dataset. I am not sure if the SMALLEST() function will work correctly. How do you want to handle ties? Here is a program that finds the second smallest value and ignores duplicate values.

%macro nextNB(ds,var);
%local dsid rc minv nv &var ;
%*----------------------------------------------------------------------
Open the dataset and link dataset variables to macro variables.
Loop until all observations are read.
-----------------------------------------------------------------------;
%let minv=.;
%let nv=.;
%let did=%sysfunc(open(&ds(keep=&var)));
%syscall set(did);
%do %while(not %sysfunc(fetch(&did)));
   %if %sysevalf(. = &minv) %then %let minv=&&&var;
   %else %if %sysevalf(&&&var <= &minv) %then %let minv=&&&var;
   %else %if %sysevalf(. = &nv) %then %let nv=&&&var;
   %else %if %sysevalf(&&&var < &nv) %then %let nv=&&&var;
%end;
%let rc=%sysfunc(close(&did));
&nv.
%mend nextnb;


data list;
 do value= 5,9,5,7,8 ; output; end;
run;
%put %nextnb(list,value);

This will result in finding 7 as the second smallest value.

If you would rather it return 5 since there are two 5's in the data then change the <= to just a <.

SAS Macro - Array

2 Answers2