topic Re: ROUTINE in SAS Programming

ROUTINE - KEEP VARIABLES FROM A DB (CAN BE DIFFERENT NR) AND EXECUTE A ROUTINE WITH EACH VARIABLE

Tecla1 — Fri, 17 Dec 2021 12:05:42 GMT

NOTE: Remote submit to SERV commencing.
582  options mprint;
583
584  %macro crea_stringa();
585
586  proc sql;
587      select name into :my_name1 - :my_name__
588      from work.my_data;
589  quit;
590
591  %put My name 1 is: &my_name1.;
592  %put My name 2 is: &my_name2.;
593
594  proc sql;
595  select count(name) as NUMBER into :count from work.my_data;
596  quit;
597          %local j;
598
599          %do j=1 %to &count.;
600
601          if prog=&j.+1 then
602
603  %let name= cats(&my_name.,&j.);
604
605  %put My name is: &name.;
606
607  retain B_&name. D_&name. P_&name.;
608  B_&name. = lag(&name.);
609  D_&name. = dif(&name.);
610  P_&name.= D_&name./B_&name.;
611  format P_&name. percent.2;
612  if D_&name. = . then D_&name. = 0;
613          %end;
614  %mend;
615
616  %crea_stringa;
MLOGIC(CREA_STRINGA):  Beginning execution.
MPRINT(CREA_STRINGA):   proc sql;
MPRINT(CREA_STRINGA):   select name into :my_name1 - :my_name__ from work.my_data;
WARNING: INTO Clause :my_name1 through :my_name__ does not specify a valid sequence of macro
         variables.
MPRINT(CREA_STRINGA):   quit;
NOTE: The PROCEDURE SQL printed page 27.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


MLOGIC(CREA_STRINGA):  %PUT My name 1 is: &my_name1.
SYMBOLGEN:  Macro variable MY_NAME1 resolves to RET_TOT
My name 1 is: RET_TOT
MLOGIC(CREA_STRINGA):  %PUT My name 2 is: &my_name2.
SYMBOLGEN:  Macro variable MY_NAME2 resolves to ESP_TOT
My name 2 is: ESP_TOT
MPRINT(CREA_STRINGA):   proc sql;
MPRINT(CREA_STRINGA):   select count(name) as NUMBER into :count from work.my_data;
MPRINT(CREA_STRINGA):   quit;
NOTE: The PROCEDURE SQL printed page 28.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


MLOGIC(CREA_STRINGA):  %LOCAL  J
SYMBOLGEN:  Macro variable COUNT resolves to        2
MLOGIC(CREA_STRINGA):  %DO loop beginning; index variable J; start value is 1; stop value is 2; by
      value is 1.
NOTE: Line generated by the invoked macro "CREA_STRINGA".
616             if prog=&j.+1 then
                --
                180
SYMBOLGEN:  Macro variable J resolves to 1
MLOGIC(CREA_STRINGA):  %LET (variable name is NAME)
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable J resolves to 1
MLOGIC(CREA_STRINGA):  %PUT My name is: &name.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
WARNING: Apparent symbolic reference MY_NAME not resolved.
My name is: cats(&my_name.,1)
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
WARNING: Apparent symbolic reference MY_NAME not resolved.
MPRINT(CREA_STRINGA):   if prog=1+1 then retain B_cats(&my_name.,1) D_cats(&my_name.,1)
P_cats(&my_name.,1);

ERROR 180-322: Statement is not valid or it is used out of proper order.

SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
NOTE: Line generated by the macro variable "NAME".
616   B_cats(&my_name.,1)
      ------
      180
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
WARNING: Apparent symbolic reference MY_NAME not resolved.
MPRINT(CREA_STRINGA):   B_cats(&my_name.,1) = lag(cats(&my_name.,1));

ERROR 180-322: Statement is not valid or it is used out of proper order.

SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
NOTE: Line generated by the macro variable "NAME".
616   D_cats(&my_name.,1)
      ------
      180
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
WARNING: Apparent symbolic reference MY_NAME not resolved.
MPRINT(CREA_STRINGA):   D_cats(&my_name.,1) = dif(cats(&my_name.,1));

ERROR 180-322: Statement is not valid or it is used out of proper order.

SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
NOTE: Line generated by the macro variable "NAME".
616   P_cats(&my_name.,1)
      ------
      180
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
WARNING: Apparent symbolic reference MY_NAME not resolved.
MPRINT(CREA_STRINGA):   P_cats(&my_name.,1)= D_cats(&my_name.,1)/B_cats(&my_name.,1);

ERROR 180-322: Statement is not valid or it is used out of proper order.

SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
NOTE: Line generated by the invoked macro "CREA_STRINGA".
616     retain B_&name. D_&name. P_&name.; B_&name. = lag(&name.); D_&name. = dif(&name.); P_&name.=
616! D_&name./B_&name.; format P_&name. percent.2; if D_&name. = . then D_&name. = 0;
                        ------
                        180
ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference MY_NAME not resolved.
MPRINT(CREA_STRINGA):   format P_cats(&my_name.,1) percent.2;

SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
NOTE: Line generated by the invoked macro "CREA_STRINGA".
616     retain B_&name. D_&name. P_&name.; B_&name. = lag(&name.); D_&name. = dif(&name.); P_&name.=
616! D_&name./B_&name.; format P_&name. percent.2; if D_&name. = . then D_&name. = 0;
                                                   --
                                                   180
ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,1)
WARNING: Apparent symbolic reference MY_NAME not resolved.
MPRINT(CREA_STRINGA):   if D_cats(&my_name.,1) = . then D_cats(&my_name.,1) = 0;

MLOGIC(CREA_STRINGA):  %DO loop index variable J is now 2; loop will iterate again.
NOTE: Line generated by the invoked macro "CREA_STRINGA".
616             if prog=&j.+1 then
                --
                180
SYMBOLGEN:  Macro variable J resolves to 2
MLOGIC(CREA_STRINGA):  %LET (variable name is NAME)
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable J resolves to 2
MLOGIC(CREA_STRINGA):  %PUT My name is: &name.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
WARNING: Apparent symbolic reference MY_NAME not resolved.
My name is: cats(&my_name.,2)
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
WARNING: Apparent symbolic reference MY_NAME not resolved.
MPRINT(CREA_STRINGA):   if prog=2+1 then retain B_cats(&my_name.,2) D_cats(&my_name.,2)
P_cats(&my_name.,2);

ERROR 180-322: Statement is not valid or it is used out of proper order.

SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
NOTE: Line generated by the macro variable "NAME".
616   B_cats(&my_name.,2)
      ------
      180
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
WARNING: Apparent symbolic reference MY_NAME not resolved.
MPRINT(CREA_STRINGA):   B_cats(&my_name.,2) = lag(cats(&my_name.,2));

ERROR 180-322: Statement is not valid or it is used out of proper order.

SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
NOTE: Line generated by the macro variable "NAME".
616   D_cats(&my_name.,2)
      ------
      180
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
WARNING: Apparent symbolic reference MY_NAME not resolved.
MPRINT(CREA_STRINGA):   D_cats(&my_name.,2) = dif(cats(&my_name.,2));

ERROR 180-322: Statement is not valid or it is used out of proper order.

SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
NOTE: Line generated by the macro variable "NAME".
616   P_cats(&my_name.,2)
      ------
      180
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
WARNING: Apparent symbolic reference MY_NAME not resolved.
MPRINT(CREA_STRINGA):   P_cats(&my_name.,2)= D_cats(&my_name.,2)/B_cats(&my_name.,2);

ERROR 180-322: Statement is not valid or it is used out of proper order.

SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
NOTE: Line generated by the invoked macro "CREA_STRINGA".
616     retain B_&name. D_&name. P_&name.; B_&name. = lag(&name.); D_&name. = dif(&name.); P_&name.=
616! D_&name./B_&name.; format P_&name. percent.2; if D_&name. = . then D_&name. = 0;
                        ------
                        180
ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference MY_NAME not resolved.
MPRINT(CREA_STRINGA):   format P_cats(&my_name.,2) percent.2;

SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
NOTE: Line generated by the invoked macro "CREA_STRINGA".
616     retain B_&name. D_&name. P_&name.; B_&name. = lag(&name.); D_&name. = dif(&name.); P_&name.=
616! D_&name./B_&name.; format P_&name. percent.2; if D_&name. = . then D_&name. = 0;
                                                   --
                                                   180
ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference MY_NAME not resolved.
SYMBOLGEN:  Macro variable NAME resolves to cats(&my_name.,2)
WARNING: Apparent symbolic reference MY_NAME not resolved.
MPRINT(CREA_STRINGA):   if D_cats(&my_name.,2) = . then D_cats(&my_name.,2) = 0;

MLOGIC(CREA_STRINGA):  %DO loop index variable J is now 3; loop will not iterate again.
MLOGIC(CREA_STRINGA):  Ending execution.
NOTE: Remote submit to SERV complete.

Buongiorno,

ho creato la seguente macro ma non riesco a capire l'errore 🙄:

- trova le variabili nel DB "my_data" e poi esegue delle differenze e dei "lag" ma non risco a fargli prendere i nomi delle variabili :

my_data:

Re: ROUTINE

PaigeMiller — Fri, 17 Dec 2021 11:59:45 GMT

First, you say there's an error, but you don't show the error. Please run this command, then run your macro again.

options mprint;

Then, show us the LOG from your run of the macro. We need to see the LOG and not the code itself. We need to see the ENTIRE log for the run of this macro, all of it, every single line, not selected parts of the log.

Please format the log to make it readable. To do this, you copy the log as text (not a screen capture) and paste it into the window that appears when you click on the </> icon (see below). DO NOT SKIP THIS STEP.

Lastly, please go back to your original post and edit it to provide a meaningful title that give a brief description of the problem, instead of a meaningless word ROUTINE.

We're going to try to help you, but you have to help us by performing the tasks mentioned above.

Re: ROUTINE

Tecla1 — Fri, 17 Dec 2021 12:07:33 GMT

Excuse me Paige Miller , I accetted a solution.... I have done everithing well?

Re: ROUTINE

PaigeMiller — Fri, 17 Dec 2021 12:10:54 GMT

Thank you for changing the title of your post. This helps everyone find posts that can be helpful to them.

Why did you accept the solution? I didn't solve anything. Unless telling you to use

options mprint;

helped you figure out what was wrong, in which case you should probably mention that.

Re: ROUTINE - KEEP VARIABLES FROM A DB (CAN BE DIFFERENT NR) AND EXECUTE A ROUTINE WITH EACH VARIABL

PaigeMiller — Fri, 17 Dec 2021 12:20:37 GMT

616             if prog=&j.+1 then
                --
                180

You can't use an IF statement, unless it is inside a DATA step. I don't know if that's what you intended to do here, in which case you need a DATA statement, and probably a SET statement, before this IF.

Can you show us a simple example of code that does what you are trying to do, that doesn't contain macros? Can you write working code as if there was only one iteration, and so the macro loop isn't needed? Show us this code without macros that works on the first iteration only and does what you want.

Re: ROUTINE

Tecla1 — Fri, 17 Dec 2021 13:03:16 GMT

No in realtà ho sbagliato ad accettare.... faccio un post nuovo?
Grazie.

Re: ROUTINE

PaigeMiller — Fri, 17 Dec 2021 13:09:37 GMT

You should be able to change my answer above from correct to not correct, there should be a button you can click on to make this happen. Nevertheless, please continue the discussion in this thread.

Re: ROUTINE - KEEP VARIABLES FROM A DB (CAN BE DIFFERENT NR) AND EXECUTE A ROUTINE WITH EACH VARIABL

Tecla1 — Fri, 17 Dec 2021 13:20:52 GMT

in allegato il programma originario che ho modificato in quanto ho da ripetere la stessa cosa su moti DB di dimensioni diverse grazie mille per la cortesia !!

NOTE: Remote submit to SERV commencing.
655  %macro agg (nome,CAM1,CAM2,aamm);
656
657  data controllo_&nome._tot;
658  set contrper.controllo_&nome._tot;
659
660
661  /*variazioni ad un mese*/
662
663  retain B_&CAM1. D_&CAM1. P_&CAM1.;
664  B_&CAM1. = lag(&CAM1.);
665  D_&CAM1. = dif(&CAM1.);
666  P_&CAM1.= D_&CAM1./B_&CAM1.;
667  format P_&CAM1. percent.2;
668  if D_&CAM1. = . then D_&CAM1. = 0;
669
670  retain B_&CAM2. D_&CAM2. P_&CAM2.;
671  B_&CAM2. = lag(&CAM2.);
672  D_&CAM2. = dif(&CAM2.);
673  P_&CAM2.= D_&CAM2./B_&CAM2.;
674  format P_&CAM2. percent.2;
675  if D_&CAM2. = . then D_&CAM2. = 0;
676
677
678  run;
679
680
681  %mend;
682
683  %agg (Cpl0102_l01,RET_TOT,ESP_TOT,2101);
MLOGIC(AGG):  Beginning execution.
MLOGIC(AGG):  Parameter NOME has value Cpl0102_l01
MLOGIC(AGG):  Parameter CAM1 has value RET_TOT
MLOGIC(AGG):  Parameter CAM2 has value ESP_TOT
MLOGIC(AGG):  Parameter AAMM has value 2101
SYMBOLGEN:  Macro variable NOME resolves to Cpl0102_l01
MPRINT(AGG):   data controllo_Cpl0102_l01_tot;
SYMBOLGEN:  Macro variable NOME resolves to Cpl0102_l01
MPRINT(AGG):   set contrper.controllo_Cpl0102_l01_tot;
SYMBOLGEN:  Macro variable CAM1 resolves to RET_TOT
SYMBOLGEN:  Macro variable CAM1 resolves to RET_TOT
SYMBOLGEN:  Macro variable CAM1 resolves to RET_TOT
MPRINT(AGG):   retain B_RET_TOT D_RET_TOT P_RET_TOT;
SYMBOLGEN:  Macro variable CAM1 resolves to RET_TOT
SYMBOLGEN:  Macro variable CAM1 resolves to RET_TOT
MPRINT(AGG):   B_RET_TOT = lag(RET_TOT);
SYMBOLGEN:  Macro variable CAM1 resolves to RET_TOT
SYMBOLGEN:  Macro variable CAM1 resolves to RET_TOT
MPRINT(AGG):   D_RET_TOT = dif(RET_TOT);
SYMBOLGEN:  Macro variable CAM1 resolves to RET_TOT
SYMBOLGEN:  Macro variable CAM1 resolves to RET_TOT
SYMBOLGEN:  Macro variable CAM1 resolves to RET_TOT
MPRINT(AGG):   P_RET_TOT= D_RET_TOT/B_RET_TOT;
SYMBOLGEN:  Macro variable CAM1 resolves to RET_TOT
MPRINT(AGG):   format P_RET_TOT percent.2;
SYMBOLGEN:  Macro variable CAM1 resolves to RET_TOT
SYMBOLGEN:  Macro variable CAM1 resolves to RET_TOT
MPRINT(AGG):   if D_RET_TOT = . then D_RET_TOT = 0;
SYMBOLGEN:  Macro variable CAM2 resolves to ESP_TOT
SYMBOLGEN:  Macro variable CAM2 resolves to ESP_TOT
SYMBOLGEN:  Macro variable CAM2 resolves to ESP_TOT
MPRINT(AGG):   retain B_ESP_TOT D_ESP_TOT P_ESP_TOT;
SYMBOLGEN:  Macro variable CAM2 resolves to ESP_TOT
SYMBOLGEN:  Macro variable CAM2 resolves to ESP_TOT
MPRINT(AGG):   B_ESP_TOT = lag(ESP_TOT);
SYMBOLGEN:  Macro variable CAM2 resolves to ESP_TOT
SYMBOLGEN:  Macro variable CAM2 resolves to ESP_TOT
MPRINT(AGG):   D_ESP_TOT = dif(ESP_TOT);
SYMBOLGEN:  Macro variable CAM2 resolves to ESP_TOT
SYMBOLGEN:  Macro variable CAM2 resolves to ESP_TOT
SYMBOLGEN:  Macro variable CAM2 resolves to ESP_TOT
MPRINT(AGG):   P_ESP_TOT= D_ESP_TOT/B_ESP_TOT;
SYMBOLGEN:  Macro variable CAM2 resolves to ESP_TOT
MPRINT(AGG):   format P_ESP_TOT percent.2;
SYMBOLGEN:  Macro variable CAM2 resolves to ESP_TOT
SYMBOLGEN:  Macro variable CAM2 resolves to ESP_TOT
MPRINT(AGG):   if D_ESP_TOT = . then D_ESP_TOT = 0;
MPRINT(AGG):   run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 1945:174   1 at 683:96
NOTE: There were 10 observations read from the data set CONTRPER.CONTROLLO_CPL0102_L01_TOT.
NOTE: The data set WORK.CONTROLLO_CPL0102_L01_TOT has 10 observations and 26 variables.
NOTE: Compressing data set WORK.CONTROLLO_CPL0102_L01_TOT increased size by 100.00 percent.
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.02 seconds


MLOGIC(AGG):  Ending execution.
NOTE: Remote submit to SERV complete.


%macro agg (nome,CAM1,CAM2,aamm);

data controllo_&nome._tot;
set contrper.controllo_&nome._tot;

/*variazioni ad un mese*/

retain B_&CAM1. D_&CAM1. P_&CAM1.;
B_&CAM1. = lag(&CAM1.);
D_&CAM1. = dif(&CAM1.);
P_&CAM1.= D_&CAM1./B_&CAM1.;
format P_&CAM1. percent.2;
if D_&CAM1. = . then D_&CAM1. = 0;

retain B_&CAM2. D_&CAM2. P_&CAM2.;
B_&CAM2. = lag(&CAM2.);
D_&CAM2. = dif(&CAM2.);
P_&CAM2.= D_&CAM2./B_&CAM2.;
format P_&CAM2. percent.2;
if D_&CAM2. = . then D_&CAM2. = 0;

run;

%mend;

%agg (Cpl0102_l01,RET_TOT,ESP_TOT,2101);

Re: ROUTINE - KEEP VARIABLES FROM A DB (CAN BE DIFFERENT NR) AND EXECUTE A ROUTINE WITH EACH VARIABL

Tom — Fri, 17 Dec 2021 13:21:25 GMT

You have an error in the first SQL step of the macro. Fix that first and then see if there are still errors.

proc sql;
     select name into :my_name1 - :my_name__
     from work.my_data;
quit;

The upper bound target macro variable name does not end with a numeric suffix.

Since you do not need to specify the upper bound just remove it.

Also your probably do not want to also print all of the names to the output destination(s) so add the NOPRINT option.

Also PROC SQL will count the number of names it found, so need to do extra work to count the names.

proc sql noprint;
   select name into :my_name1 - 
     from work.my_data
  ;
%let count=&sqlobs;
quit;

Re: ROUTINE - KEEP VARIABLES FROM A DB (CAN BE DIFFERENT NR) AND EXECUTE A ROUTINE WITH EACH VARIABL

Tecla1 — Fri, 17 Dec 2021 13:28:36 GMT

Ciao, il mio problema è soprattutto come inserire queste variabili nel programma che deve "girare" quante volte sono le variabili in "MY_name":

proc sql;
    select name into :my_name1 - :my_name__
    from work.my_data;
quit;
 
%put My name 1 is: &my_name1.;
%put My name 2 is: &my_name2.;
 
proc sql; 
select count(name) as NUMBER into :count from work.my_data;
quit;

data xx;

		%local j;
		
		%do j=1 %to &count.;

		if prog=&j.+1 then 


retain B_&my_name1. D_&my_name1. P_&my_name1.;
B_&my_name1. = lag(&my_name1.);
D_&my_name1. = dif(&my_name1.);
P_&my_name1.= D_&my_name1../B_&my_name1.;
format P_&my_name1. percent.2;
if D_&my_name1. = . then D_&my_name1. = 0;
		%end;
run;

Re: ROUTINE - KEEP VARIABLES FROM A DB (CAN BE DIFFERENT NR) AND EXECUTE A ROUTINE WITH EACH VARIABL

PaigeMiller — Fri, 17 Dec 2021 13:32:16 GMT

In the version of the program that you showed a few minutes ago, there is a DATA statement and a SET statement. In the code you started this thread with, there is no DATA statement and no SET statement, and the code needs to have these.

Re: ROUTINE - KEEP VARIABLES FROM A DB (CAN BE DIFFERENT NR) AND EXECUTE A ROUTINE WITH EACH VARIABL

Tecla1 — Fri, 17 Dec 2021 13:35:17 GMT

Ciao, ho capito, io stavo tentando di fare un programma nuovo e diverso, quello che ti ho mandato è il programma vecchio che mi restituisce un risultato che è il mio obiettivo. Nel nuovo programma non riesco a fare la routine ed ad inserire le variabili.

Re: ROUTINE - KEEP VARIABLES FROM A DB (CAN BE DIFFERENT NR) AND EXECUTE A ROUTINE WITH EACH VARIABL

Tom — Fri, 17 Dec 2021 14:01:13 GMT

The SAS compiler does not start building the data step until after the macro processor has finished generating all of the statements for the data step.

The key thing with macro code is to know what code you want to generate.

For this problem it looks like you need to generate code like:

B_Age = lag(Age);
D_Age = coalesce(dif(Age),0);
P_Age = divide(D_Age,B_Age);
format P_Age percent7.2;

So structure your macro code to generate that code.

%macro test(dsin,dsout,namelist);
%local count j namelist name ;

proc sql noprint;
 select nliteral(name) into :namelist separated by '|'
   from &namelist
 ;
 %let count=&sqlobs;
quit;
 

data &dsout;
  set &dsin;

%do j=1 %to &count;
  %let name=%scan(&namelist,&j,|);

  B_&name = lag(&name);
  D_&name = coalesce(dif(&name),0);
  P_&name = divide(D_&name,B_&name);

  format P_&name. percent7.2;
%end;

run;

%mend test;

Let's generate some test inputs and call the macro.

data test;
  set sashelp.class;
  keep _numeric_;
run;
proc contents data=test noprint out=contents(keep=name); 
run;
options mprint;
%test(dsin=sashelp.class,dsout=xx,namelist=contents)

Resutls:

363   options mprint;
364   %test(dsin=sashelp.class,dsout=xx,namelist=contents)
MPRINT(TEST):   proc sql noprint;
MPRINT(TEST):   select nliteral(name) into :namelist separated by '|' from contents ;
MPRINT(TEST):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


MPRINT(TEST):   data xx;
MPRINT(TEST):   set sashelp.class;
MPRINT(TEST):   B_Age = lag(Age);
MPRINT(TEST):   D_Age = coalesce(dif(Age),0);
MPRINT(TEST):   P_Age = divide(D_Age,B_Age);
MPRINT(TEST):   format P_Age percent7.2;
MPRINT(TEST):   B_Height = lag(Height);
MPRINT(TEST):   D_Height = coalesce(dif(Height),0);
MPRINT(TEST):   P_Height = divide(D_Height,B_Height);
MPRINT(TEST):   format P_Height percent7.2;
MPRINT(TEST):   B_Weight = lag(Weight);
MPRINT(TEST):   D_Weight = coalesce(dif(Weight),0);
MPRINT(TEST):   P_Weight = divide(D_Weight,B_Weight);
MPRINT(TEST):   format P_Weight percent7.2;
MPRINT(TEST):   run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.XX has 19 observations and 14 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

Re: ROUTINE - KEEP VARIABLES FROM A DB (CAN BE DIFFERENT NR) AND EXECUTE A ROUTINE WITH EACH VARIABL

Tecla1 — Fri, 17 Dec 2021 14:08:44 GMT

Grazie !!!! Siete davvero preziosi !!!!! Buone feste !!!!