topic Re: Help in Macro loop in SAS Programming

Help in Macro loop

Kalai2008 — Sun, 27 Aug 2017 17:45:00 GMT

This macro works fine. I am trying to create a macro and pass on the ID to rest of the program.

data WANT (keep=id);
set have nobs=_nobs_;
count = ceil(_n_ / 1000);
run;

Dataset want has column called ID( 6500 ids).Created a variable called count, setting to 1000 ids in each count.

Created a global variable Check.

This macro works fine when the loop is 1 to 7 (each check contains 1000), but If I increment from 7 to 10, the rest of the 3 increments (8,9,10) gets repeated (having the same set of ids from the last iterations).

I need help here. I wanna tell the macro to stop working when it reaches the last obs in the dataset WANT. Iteration J should stop looping. When it reaches the last observation , the iteration also should stop. if its 7 or 8 then stop till 7 or 8.

I dont want to change the iterations from 10 to 7. Because I want to use the same program again & my next dataset have more ids( more than 8000). I dont want to keep changing the numbers in interation.

%macro tests;
%do j = 1 % to 10 ;
%global check_&j;
proc sql noprint;
select id into: check separated by ","
from WANT
where count = &j;
quit;
%let check_&j = (&check);
%put check_&j= &&check_&j;
%end;
%mend;
%create_tests;

Re: Help in Macro loop

Reeza — Sun, 27 Aug 2017 18:00:53 GMT

Are you trying to split data into subsets of 1000 rows?

I don't think you need a macro for that.

Re: Help in Macro loop

Kalai2008 — Sun, 27 Aug 2017 18:08:52 GMT

I am trying to pass ids to Oracle SQL in where condition.

Proc sql;

connect to oracle(user=&userid. password=&passwd. path= preserve_comments);

Re: Help in Macro loop

Reeza — Sun, 27 Aug 2017 18:23:16 GMT

OK... I still probably wouldn't use a macro, but would use a data step with a single loop through. At each 1000 or end then you create the macro variable that you've been collecting until the end.

Here's a start, I don't have more time to tweak this but think it works the way you want. This is fully dynamic based on your dataset and size. You could wrap it in a macro I suppose.

*create a sample data set;
data have;
    do i=1 to 7000;
        x=rand('bernoulli', 0.4);
        output;
    end;
run;

*Set group size;
%let group_size = 1000;

*Creates a data set, but not required can be a data _null_ data step;
data want;

    set have end=_eof nobs=_nobs;
    length list_names $32000.;
    retain group 0 list_names;

    prev_group=group;
    
    if mod(_n_, &group_size)=0 then group+1;
    
    if group ne prev_group or _eof then do;
        if _eof then list_names = catx(", ", list_names, put(x, 8.2));
        call symputx("list"||put(group, z2.), list_names, 'g');
        call missing(list_names);
    end;

        list_names = catx(", ", list_names, put(x, 8.2));

    
run;

%put &list01;
%put &list04;

Re: Help in Macro loop

Kalai2008 — Sun, 27 Aug 2017 18:36:34 GMT

Thank you, I will try this!

Re: Help in Macro loop

Patrick — Sun, 27 Aug 2017 23:24:56 GMT

@Kalai2008

If you explain us a bit better what you have and what you're trying to achieve then we can may-be provide better answers.

I.e: You don't need to count observations in SAS tables as the number of observations is a data set attribute. So there is no need to have a full pass through your data for every single iteration of your macro loop.