BookmarkSubscribeRSS Feed
Kalai2008
Pyrite | Level 9

 

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;

5 REPLIES 5
Reeza
Super User

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

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

Kalai2008
Pyrite | Level 9

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

Proc sql;

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

Reeza
Super User

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;
    
    

 

Kalai2008
Pyrite | Level 9
Thank you, I will try this!
Patrick
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 733 views
  • 0 likes
  • 3 in conversation