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;
Are you trying to split data into subsets of 1000 rows?
I don't think you need a macro for that.
I am trying to pass ids to Oracle SQL in where condition.
Proc sql;
connect to oracle(user=&userid. password=&passwd. path= preserve_comments);
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.