How to loop through a proc sql select statement result

[L2Fly]

Hello everyone - sorry if this question is way to simple.  I am very new into SAS coding and no knowledge of SAS Macros and it's syntax.  What I like to do is to select two columns from a table based on a where condition, then do a loop through the result-set (or while I am selecting the record) and pass the two variables for each observation as a parameter into a macro.

PROC SQL noprint;
CREATE TABLE WORK.MyTableResult AS
SELECT t1.CODE,
               t1.CODE_Name
FROM    MyLIB.CODETABLE t1
WHERE t1.CODE = 'IPKEY';
QUIT;

Say I have 20 observations and now for each one I like to pass their value into my macro

%myMacro (code, code_name);

thanks for your help

[Reeza]

You can create a string variable using concatenate type functions that looks like your macro call. THen you can pass it to CALL EXECUTE or DOSUBL and SAS will execute the code in the variable. 

Example 1: 

https://gist.github.com/statgeek/beb97b1c6d4517dde3b2

Example 2:

Turning a program into a macro - only last step here is relevant to your question

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

---

@L2Fly wrote:

Hello everyone - sorry if this question is way to simple.  I am very new into SAS coding and no knowledge of SAS Macros and it's syntax.  What I like to do is to select two columns from a table based on a where condition, then do a loop through the result-set (or while I am selecting the record) and pass the two variables for each observation as a parameter into a macro.

PROC SQL noprint;
CREATE TABLE WORK.MyTableResult AS
SELECT t1.CODE,
               t1.CODE_Name
FROM    MyLIB.CODETABLE t1
WHERE t1.CODE = 'IPKEY';
QUIT;

Say I have 20 observations and now for each one I like to pass their value into my macro

%myMacro (code, code_name);

thanks for your help

---

[L2Fly]

Thank you @Reeza for your help and info.  Here is my code that I have.  In green I explained what I like to pass to the macro.  From your second link, I can see it is creating a table called car_makes but on the second part "data macro_call; I don't understand the "build macro call string" part.

I want to schedule this code and do the following based on a table.

%let _ENCODING=UTF-8;
%macro disablelisting;
/* Disable listing file output */
filename nulpath dummy;
proc printto print = nulpath;
run;
%mend;
%disablelisting;

options VALIDVARNAME=ANY VALIDMEMNAME=EXTEND;
/* Status Checkpoint Macro */
%macro statuscheckpoint(maxokstatus=4, varstocheck=SYSERR SYSLIBRC SYSDBRC );

%GLOBAL LASTSTEPRC;
%LET pos=1;
%let var=notset;
%let var=%SCAN(&varstocheck.,&pos.);
%DO %WHILE ("&VAR." ne "");
/* Retrieve the next return code to check */
%if (%symexist(&VAR.)) %then %do;
%let val=&&&VAR..;
%if (("&VAL." ne "") and %eval(&VAL. > &maxokstatus.)) %then %do;
%put FAIL = &VAR.=&VAL. / SYSCC=&SYSCC.;
%let LASTSTEPRC=&VAL.;
%end;
%end;
%let pos = %eval(&pos.+1);
%let var=%SCAN(&varstocheck.,&pos.);
%END;
%mend;
%statuscheckpoint;
/* Skip Next Step If We Have a Bad Status Code */
%macro codeBody;
%GLOBAL LASTSTEPRC;
%if %symexist(LASTSTEPRC) %then %do;
%if %eval(&LASTSTEPRC. <= 4) %then %do;

LIBNAME ADHLASR SASIOLA TAG=ADHLASR PORT=99999 SIGNER="https://MyServerName.com:443/SASLASRAuthorization" HOST="MyServer.com" ;

/* Remove data from the server */
%macro deletedsifexists(lib,name);
%if %sysfunc(exist(&lib..&name.)) %then %do;
proc datasets library=&lib. nolist;
delete &name.;
quit;
%end;
%mend deletedsifexists;

/*

Here I want to through the list of observation (records), get and pass two variables from my select statement into the %deletedsifexists

*/
%deletedsifexists(ADHLASR, SASTESTING);
%deletedsifexists(ADHLASR, JUNK);

%end;
%end;
%mend;
%codeBody;

[Reeza]

I'm assuming you ran the code and examined the data set? That should make it pretty clear, but take a look and let me know because that's really the only step you need.

[L2Fly]

Thanks and yes I did try to go over the lines of first example link.  This is what I understand from it:

1- it creates a macro with two parameters which it will prints the sashelp.class with two where parameters.

what is little confusing for me the third section "data sample;".  I think on the second step sorts the data by age and sex and call it class.  Then in the third step it uses the class (records) and calling the summary macro passing age and sex parameters.  Am I correct?  

This is what I don't understand because I get an error message 

*get list of all makes;
proc sql;
create table car_makes as
select distinct make
from sashelp.cars;
quit;

data macro_call;
set car_makes (obs=5); *run only 5 for testing;

*build macro call string;
str = catt('%report_make(r_make =', make, ');');

*call macro;
call execute(str);

run;

[Reeza]

What error? Run the code from previous step to create the macro and test the code. Or comment out call execute. 

[L2Fly]

Hello @Reeza, thanks for your input and help on this.  I did try and modified my code.  I have a comment on the code where I am calling the macro manually it does work and it unload the table from ADHLASR LASR library.

/*%deletedsifexists(ADHLASR, JUNK);         --it does works like this and it does unload the table from LASR*/
/*%deletedsifexists(ADHLASR, SASTESTING);   --it does works like this and it does unload the table from LASR*/

But when I run this statement it runs, no warning or error messages but it does not unload the table

data _null_;
set work.mytable;
   call execute('%deletedsifexists('||ADHLASR||','||STRING1_VALUE||')');  /* This will run, no error or warning but it does not unload the table */
run;

Here is the full program, again much appreciated for any feedback and help.

%let _ENCODING=UTF-8;                                                           

libname LIBPROD meta Library = EPROD;

/* The following query will return two observation.  They are called SASTESTING and JUNK.  
   This is the name of the two tables that I need to unload using the following Macro */
PROC SQL noprint;
	CREATE TABLE work.mytable AS 
		SELECT STRING1_VALUE
		FROM LIBPROD.REPORTDATA lp
			WHERE lp.LIST_NAM = 'SASLASRDataLoads'
            AND lp.STRING5_VALUE = 'true';
QUIT;


/*%macro disablelisting;                                                          */
/*   /* Disable listing file output */                                            */
/*filename nulpath dummy;                                                         */
/*proc printto print = nulpath;                                                   */
/*run;                                                                            */
/*%mend;                                                                          */
/*%disablelisting;                                                                */
                                                                                
options VALIDVARNAME=ANY VALIDMEMNAME=EXTEND;                                   
/* Status Checkpoint Macro */     
%macro statuscheckpoint(maxokstatus=4, varstocheck=SYSERR SYSLIBRC SYSDBRC );   
                                                                                
   %GLOBAL LASTSTEPRC;                                                          
   %LET pos=1;                                                                  
   %let var=notset;                                                             
   %let var=%SCAN(&varstocheck.,&pos.);                                         
   %DO %WHILE ("&VAR." ne "");                                                  
      /* Retrieve the next return code to check */                              
	  %if (%symexist(&VAR.)) %then %do;                                            
	     %let val=&&&VAR..;                                                        
	     %if (("&VAL." ne "") and %eval(&VAL. > &maxokstatus.)) %then %do;         
		    %put FAIL = &VAR.=&VAL. / SYSCC=&SYSCC.;                                  
           %let LASTSTEPRC=&VAL.;                                               
		 %end;                                                                        
	  %end;                                                                        
	  %let pos = %eval(&pos.+1);                                                   
      %let var=%SCAN(&varstocheck.,&pos.);                                      
   %END;                                                                        
%mend;                                                                          
%statuscheckpoint;                                                              
/* Skip Next Step If We Have a Bad Status Code */                               
%macro codeBody;                                                                
   %GLOBAL LASTSTEPRC;                                                          
%if %symexist(LASTSTEPRC) %then %do;                                            
   %if %eval(&LASTSTEPRC. <= 4) %then %do;                                      
  
 /* the following statement is modified for privacy reason */                                                                             
   LIBNAME ADHLASR SASIOLA  TAG=ADHLASR  PORT=11111 SIGNER="https://MySASServer.com:443/SASLASRAuthorization"  HOST="MySASSeerver.com" ;       
                                                                                                                                                                
/* Remove data from the server */                                                                                                                               
%macro deletedsifexists(lib,name);                                                                                                                              
   %if %sysfunc(exist(&lib..&name.)) %then %do;                                                                                                                 
      proc datasets library=&lib. nolist;                                                                                                                       
delete &name.;                                                                                                                                                  
quit;                                                                                                                                                           
%end;                                                                                                                                                           
%mend deletedsifexists;                                                                                                                                         


data _null_;
set work.mytable;
   call execute('%deletedsifexists('||ADHLASR||','||STRING1_VALUE||')');  /* This will run, no error or warning but it does not unload the table */
run;
 
/*%deletedsifexists(ADHLASR, JUNK);         --it does works like this and it does unload the table from LASR*/
/*%deletedsifexists(ADHLASR, SASTESTING);   --it does works like this and it does unload the table from LASR*/
                                                                                                                                                                
%end;                                                                                                                                                           
%end;                                                                                                                                                           
%mend;                                                                                                                                                          
%codeBody;                                                                                                                                                      
                                                                                                                                                                

[Reeza]

Create a string variable that holds the inside of the call execute and check that first.

[L2Fly]

Thank you @Reeza, can you please give me an example? 

[Reeza · Accepted solution]

The links were examples. 

Specifically for your code:

data _null_;
set work.mytable;
   call execute('%deletedsifexists('||ADHLASR||','||STRING1_VALUE||')');  /* This will run, no error or warning but it does not unload the table */
run;

Change the data null to a data step so you see the values.

Move the string inside the call execute to create a character variable using CATT). || can introduce extra spaces and mess things up.

Pass the character variable to call execute instead of the string directly. 

[L2Fly]

Thank you @Reeza, got it and it is working.

Best