Macro: What does this mean?

[Vinz867]

I am trying to figure out what this macro is doing:

%macro getData;

proc sql;
create table data as
(
select distinct ID, NAME
from source
);
quit;

%countObs(id,Cnt);
%loopCount(&Cnt,25);
%put &loopCount;

%do j=1 %to &loopCount;
proc sql;

create table data as
(
select distinct ID
from data
);
quit;

data data_X junk;
set data;
if _n_ <=25 then output junk;
else output data_X;
run;

data _null_;
set junk end=eof;
var=compress('var'||_n_);
call symputx(var,ID);
if eof then call symputx('obsCount',_n_);
run;
%put &obsCount;

data _null_;
x=("('"
%do i=1 %to &obsCount;
||"&&var&i"||"','"
%end;
||')');
x=tranwrd(x,",')",")");
call symputx("dd&j",x);
run;
%put &&dd&j;

%mend;

[Tom]

Do you know what those other macros that it is calling do?

%countObs(id,Cnt);
%loopCount(&Cnt,25);

The bottom part seems to be working really hard to put something into a series of macro variables with numeric suffixes on their names.  DD1, DD2, etc.  It does not seem to do anything with them however.  So my guess it that is the goal.  If so it really seem way too complicated as generally you could just do that in a single data step without all of the other misdirections..

And generally I find that taking a lot of data out of datasets and into macro variables just makes it harder to do whatever analysis it is you are trying to do.

[Tom]

Since it is missing a %END it does nothing.

ERROR: There were 1 unclosed %DO statements.  The macro GETDATA will not be compiled.
ERROR: A dummy macro will be compiled.

[PaigeMiller]

I am trying to figure out what this macro is doing:

Please tell us or show us what you see this macro is doing when you run it. At the very least, please turn on macro debugging options by running this code

options mprint;

and then run the macro again. By looking at the log, can you figure out what the macro is doing? Maxim 2: Read the Log.

If you still don't understand, show us the log for the running of this macro by copying it as text, and pasting it into the window that appears when you click on the </> icon. Please do not skip this step.

Also, @Tom has provided excellent advice which you should keep in mind: "And generally I find that taking a lot of data out of datasets and into macro variables just makes it harder to do whatever analysis it is you are trying to do."

[RichardAD]

It looks like the code is creating a quoted IN list of names for each ID.  There are likely much better ways to construct such lists and possibly ways to not even require them.

consider this one pass approach

data _null_ ;
  length inlist $32767 ;
  do until (last.id) ;
    set have ;
    by id ;
    inlist = catx(',', inlist, quote(trim(name))) ;
  end ;
  call symputx (cats(id,'_inlist'), inlist) ;
run ;  

* Suppose there was an ID = 'A' in the data set have ;

%put NOTE: &=A_INLIST ;

If the purpose to create a list of quoted values across a row you can do the following

array myarray <var-1> ... <var-n>
length q1-qn $200 ;
array myqarray q1 ... q-n ;
do over myarray ; myqarray[_i_] = quote(trim(myarray)) ; end ;
call symputx (cats(id,'_inlist'), catx(',', of myqarray(*)) ;

[ballardw]

Learning point for when you write your macros: comments to describe what the macro does and what the required input parameters should be are part of writing the code.

Obscure magic numbers such as 25 should be discussed as to why they are there such as

if _n_ <=25 then output junk;

Note that reusing the same data set, overwriting the results as this code does means that if something happened in the first sql create table data you have seriously complicated where the problem may have occurred because the second sql overwrites the set Data.

If you need to change a piece of code later you may have forgotten all the tricky bits of a particular macro, or other program code, and lack of comments may mean that you spend a very long time figuring out why that 25 was involved at all for example.

Unless that %countobs macro is very interesting you also not need some of that at all. There is an automatic SAS supplied macro variable every time you Proc SQL that counts the output observations named SQLOBS.

So test this code and see if the value of MYCNY happens to match your value of &CNT from that %countobs macro:

proc sql;
create table data as
(
select distinct ID, NAME
from source
);
quit;
%let MyCnt=&sqlobs.;

I assign the SQLOBS value to a new macro variable immediately after the SQL step because the next call to Proc SQL is extremely likely to overwrite the value of SQLOBS and if I need to use it elsewhere that is not good.

You may also be surprised to find out that the SQL INTO can also create numbered lists of macro variables

proc sql noprint;
   select distinct quote(strip(name)) into : name1- 
   from sashelp.class
   ;
quit;

%put _user_;  

The quote and strip function mean the the value of name has no leading or trailing spaces inside the quote values of each name from the data set. Using the Into : Name1- construct means the list of macro variable names starts with Name1 and goes until the data is exhausted. The %put _user_ will display all user created macro variables currently defined so you can see them. If you have been running a lot of code with macro variables like this it may take a bit of searching in the LOG to find the specific ones, in this case Name1 through Name19.