How do I use macros to generate datasets from a list generated from a table?

[6757390]

I have a built a macro that converts a Table into a list; which in turn is used in another macro that the goal of is to execute a %Do %While loop across the list of variables to create datasets until all they are all generated.  The only difference is the form name, so basically it substitutes in the new character variables, creates the new dataset then moves on.  It is a necessary first step to transfer data from the SQL Server into the SAS Library as a dataset.

I've incorporated what I believe to be two different and equally valid methods of generating an output (italics and bolded below) but neither results in an output.  There are no errors or warnings on the code, giving me no hint as to where to look next.

Here is my current code:

%MACRO GetList(InDS=WEBTABLE.FORMLIST, VAR=FORM, OutStr=FrmLst);
%IF %SYMEXIST(&OutStr)=0 %THEN %DO;
%GLOBAL &OutStr;
%END;
Proc SQL NOPRINT;
SELECT &VAR INTO WORK.FRMLST:&OutStr SEPARATED BY ' '
FROM &InDS;
QUIT;
%MEND GetList;

%MACRO px;
%LET FORMS = &FrmLst;

%LOCAL NEXT_VALUE;
%DO %WHILE (%SCAN(%FORMS(I=1 %TO &NOOBS)));
%LET NEXT_VALUE = %SCAN(&FORMS(I=1 %TO &NOOBS));

DATA WORK.DATA&NEXT_VALUE;
SET SASDEV.BETH_&NEXT_VALUE_PREP;
RUN;

%LET TRANSFER = CALL SYMPUT(&PX, &FORMS||LEFT(_N_));
DATA WORK.DATA&TRANSFER;
SET SASDEV.BETH_&TRANSER_PREP;
RUN;

%END;
%MEND px;

[Reeza]

Show how you called the macro.

[6757390]

Here's the call code I used:

DATA _NULL_;
CALL EXECUTE("&PX");
RUN;

Here's the log:

options mprint mlogic symbolgen;
42
43 %MACRO GetList(InDS=WEBTABLE.FORMLIST, VAR=FORM, OutStr=FrmLst);
2 The SAS System 12:08 Thursday, February 25, 2021

44 %IF %SYMEXIST(&OutStr)=0 %THEN %DO;
45 %GLOBAL &OutStr;
46 %END;
47 Proc SQL PRINT;
48 SELECT &VAR INTO WORK.FRMLST:&OutStr SEPARATED BY ' '
49 FROM &InDS;
50 QUIT;
51 %MEND GetList;
52
53 %MACRO PX;
54 %LET FORMS = &FrmLst;
55
56 %LOCAL NEXT_VALUE;
57 %DO %WHILE (%SCAN(%FORMS(I=1 %TO &NOOBS)));
58 %LET NEXT_VALUE = %SCAN(&FORMS(I=1 %TO &NOOBS));
59
60 DATA WORK.DATA&NEXT_VALUE;
61 SET SASDEV.BETH_&NEXT_VALUE_PREP;
62 RUN;
63 %LET TRANSFER = CALL SYMPUT(&PX, &FORMS||LEFT(_N_));
64 DATA WORK.DATA&TRANSFER;
65 SET SASDEV.BETH_&TRANSER_PREP;
66 RUN;
67 %END;
68 %MEND PX;
69
70 DATA _NULL_;
71 CALL EXECUTE("&PX");
WARNING: Apparent symbolic reference PX not resolved.
72 RUN;

WARNING: Apparent symbolic reference PX not resolved.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: CALL EXECUTE generated line.
NOTE: Line generated by the CALL EXECUTE routine.
1 + &PX
_
180
WARNING: Apparent symbolic reference PX not resolved.
ERROR 180-322: Statement is not valid or it is used out of proper order.

73
74
75
76
77
78 GOPTIONS NOACCESSIBLE;

79 %LET _CLIENTTASKLABEL=;
80 %LET _CLIENTPROCESSFLOWNAME=;
81 %LET _CLIENTPROJECTPATH=;
82 %LET _CLIENTPROJECTNAME=;
83 %LET _SASPROGRAMFILE=;
84
3 The SAS System 12:08 Thursday, February 25, 2021

85 ;*';*";*/;quit;run;
86 ODS _ALL_ CLOSE;
87
88
89 QUIT; RUN;

[Tom]

---

@6757390 wrote:

Here's the call code I used:

DATA _NULL_;
CALL EXECUTE("&PX");
RUN;

---

That is wrong in three different ways. 

First to call a macro use %.  The & is for referencing a (macro) variable.

Second there is no need wrap the call to the macro inside a data step.  So just use:

%px ;

And finally if you are going to call a macro that has logic that decides what code to generate based on the results of earlier SAS code that it generates (like your %GETLIST() macro) then when you call it with CALL EXECUTE you need to wrap the macro name inside of %NRSTR() so that the CALL to the macro is pushed onto the stack to execute after the data step. Otherwise the macro runs immediately and the SAS code it generates is pushed onto the stack to run later.  So any decisions the macro makes about what code to do is made before that stacked code has a chance to run.  For example if you want to call %mymacro() once for each value of MY_DS_VARIABLE in the dataset LIST your could run code like this:

data _null_;
  set list;
  call execute('%nrstr(%mymacro)(',my_ds_variable,')');
run;

[PaigeMiller]

A couple of things.

First step in getting macros to work is to have working code on (for example) two cases, hard-coded, without macros. If you get that to work without macros, then you should be able to get a macro to work. If you don't have that working without macros, then a macro will never work. Do you have such code working without macros? Can we see it?

When macro code isn't working, you need to use this command

options mprint mlogic symbolgen;

at the start of your program, run it again and look at the log. This writes a lot of useful information to the log to help you debug what is happening. If that doesn't help you when you look at the log, then SHOW US THE LOG following these instructions.

Lastly, you have in one place %FORMS when it should be &FORMS. You refer to macro variables &next_value_prep and &px and &transfer_prep and &frmlist but these don't seem to be defined anywhere.

[6757390]

I corrected the &FORMS...thanks!

[Shmuel]

1) %GLOABAL is a declarative and not executable statement; It is not affected by %IF statement.

2) The usage of SELECT ... INTO needs be followed by a name of a macro variable.

     Your code: INTO WORK.FRMLST:&OutStr  - defines an invalid name.

3) You defined macros GETLIAT and PX but there is no invoking of those macros:

    %getlist(...);,   %px;

4) In your code: %LET FORMS = &FrmLst; - but there is no assignment of value to a macro variable: %LET frmlist = ????;  nor argument called FRMLIST=<value> in PX macro.

I suggest to explain/clarify what you want to do and the forum will suggest you methods and solutions.

[6757390]

There are two essential pieces; part one is creating distinct list of variables from a table (type = character) separated by a space as a delimiter.  Part two is a macro that runs a Do While loop across the list of variables until each variable has been substituted.

Ultimately the goal is Execute so that these macros can bring in all the data from SQL Server into SAS for further processing.  The only differences is the Form Name, so creating a means to efficiently substitute that name in both the data line and the set line is the target.

Thank you!

[Tom]

---

@Shmuel wrote:

1) %GLOABAL is a declarative and not executable statement; It is not affected by %IF statement.

---

Actually no.  The %GLOBAL statement will actually execute and create an entry in the global symbol table for any macro variable that is not already there.

[6757390]

Does your correction mean that the first macro is correct? Will the FrmLst be created from the table, with each variable in the column converted to a list separated by a space? Even when I state "PRINT" there is no output for me to verify.

[Shmuel]

@Tom , I believe you are right, but as the code is doing a loop, then first time the %GLOBAL macro is executed then it will be present even for the following iterations.

(I hope I expressed myself clearly) - Therefore it seems to behave as declarative.

Am I not right?

[Tom]

Sounds like you are saying it is redundant to repetitively declare the same macro variable as global over and over.  So for example if it is done inside of a %DO loop then it should really be moved to before the %DO loop.

[Shmuel]

Hi @Tom , you are right for the 1st part and I'm right for the 2nd part, as shown in next log:

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         %macro test(x);
 74            %if &x=1 %then %do;
 75                %global glb;
 76            %end;
 77            %let glb=AAA;
 78         %mend;
 79         %put GLB=&glb;
 WARNING: Apparent symbolic reference GLB not resolved.
 GLB=&glb
 80         %test(3);
 81         %put GLB=&glb;
 WARNING: Apparent symbolic reference GLB not resolved.
 GLB=&glb
 82         
 83         %test(1);
 84         %put GLB=&glb;
 GLB=AAA
 85         
 86         %test(2);
 87         %put GLB=&glb;
 GLB=AAA
 88