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.
... View more
