topic Macro not working in New SAS User

Macro not working

hjkaufma — Fri, 10 Feb 2023 00:23:59 GMT

Hello,

I am trying to create a list of table names so that I can make a do-until loop that goes through each table and creates a new table that separates specific years into new tables. The imports work, the list works, and both proc sqls work until I added the do until loop. My goal is: for every table, create a new table by year until there are no more tables left to iterate. This is what I have so far:

(There probably is a better way to make a list for the existing tables, but with the name it should still work).

data work.tables;
input table_name $;
cards;
data1
data2
data3
;
run;

proc sql;
select distinct table_name into: table_list separated by ','
from tables;
quit;

%macro loop(current_table_list = table_list, obs = 3);
%local i next_table;
%let i=1;
%let next_table = %scan(&current_table_list, &i, %str());
%do %until(%superq(next_table) eq %str());

proc sql;
select distinct put(year, 4.) into :yearList separated by ','
from &next_table.
where year >= 1940 and year <= 1980;
quit;

%let column = put(year, 4.);
%let table = work.&next_table.;

proc sql noprint;
select distinct
cat("DATA year_", &next_table., compress(&column.,,'kad'), "; set &table.
(where=(&column.='", &column., "')); run;") length = 500 into :allsteps
separated by ';' from &table. where year >= 1940 and year <= 1980;
quit;

&allsteps.;

%let i = %eval(&i + 1);
%let next_table = %scan(&current_table_list, &i, %str());
%end;
%mend;

This is the output I get OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

No output or results.

Re: Macro not working

Astounding — Fri, 10 Feb 2023 04:23:40 GMT

Not sure this solves everything, but it's a good start.

You define a macro %loop, but you never execute the macro. Where's the %loop statement?

Adding it may generate a bunch of errors, so the debugging is just beginning. For example, macro language will not execute the PUT function. (Look at the statement assigning a value to &column.) At any rate, start by actually executing your macro and see where that leads.

Re: Macro not working

andreas_lds — Tue, 14 Feb 2023 06:08:42 GMT

The default note about creating macros: Start with working code that solves the problem for one year. Then think about macros.

The default note about asking for help: Post the log. The complete log. And, if macros are used, always activate the options mprint and mlogic, so that the log show what the macro did actually.

Why do you have to create that many datasets at all?

Re: Macro not working

fja — Tue, 14 Feb 2023 06:39:47 GMT

@hjkaufma wrote:
This is the output I get OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

No output or results.

Hello hjkaufma!

There is defenitely more in the log ... you should post that to help us helping you (as @andreas_lds has already pointed out).

You should see the sql statements being executed and the macro code being digested (but not run, as @Astounding has detailed).

I can only agree ... you are not executing the macro, so whatever you see as/in tables are probably results from previous runs.

--FJa

Re: Macro not working

ballardw — Tue, 14 Feb 2023 07:51:59 GMT

When it comes to getting lists of table names then you might consider the Dictionary tables, especially if the objective is to work with "every set" in a library.

You get the library name and data set name similar to

Proc sql;
   create table setnames as
   select libname, memname
   from dictionary.tables
   where libname='MYLIB' and memtype='Data'
   ;
run;

You should investigate CALL EXECUTE to write code with stuff in a data set. You use the variable names holding the bit to write the syntax. Call Execute places code into stack that executes after a data step ends. That is basically what your

select distinct
cat("DATA year_", &next_table., compress(&column.,,'kad'), "; set &table.
(where=(&column.='", &column., "')); run;")

does if I understand what you are attempting.

I suspect that you have a problem here:

(where=(&column.='", &column., "'))

If &column is put(year, 4.) . The macro definition means that is the text

then the above resolves to

(where=(put(year,4.)='", &column., "'))

and even a later resolution of &column is going to quotes, commas and spaces and so is very unlikely to ever result in the Where returning anything.

Once you have set candidate years instead of writing a bunch of where dataset option data steps, that will require reading each record from each data set multiple times you would be better off, if this is really needed, to write one data step that look something like

data  table1940
         table1941
         table1942
...
         table1980;
    set table;
    select year;
        when(1940) output table1940;
        when(1941) output table1941;
        when(1942) output table1942;
...
        when(1980) output table1980;
        otherwise;
     end;
run;

I have a very hard time seeing where creating all of these data sets is an advantage.