DATA Step, Macro, Functions and more

Help in Macro loop

Reply
Frequent Contributor
Posts: 94

Help in Macro loop

 

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;

Super User
Posts: 24,010

Re: Help in Macro loop

Posted in reply to Kalai2008

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

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

Frequent Contributor
Posts: 94

Re: Help in Macro loop

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

Proc sql;

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

Super User
Posts: 24,010

Re: Help in Macro loop

Posted in reply to Kalai2008

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;
    
    

 

Frequent Contributor
Posts: 94

Re: Help in Macro loop

Thank you, I will try this!
Respected Advisor
Posts: 4,797

Re: Help in Macro loop

Posted in reply to Kalai2008

@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.

Ask a Question
Discussion stats
  • 5 replies
  • 185 views
  • 0 likes
  • 3 in conversation