Macro Inline Function

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Macro Inline Function

Hi everyone

I have to program a macro function that receive a parameter and return an attribute found in a table.  This function will be called inline in other programs such a proc sql or datastep

This is the program I made

libname qdata "C:\SAS\qdata";

%macro f_tipo_telefono (ptelefono);

   %let vmaxlen=7;

   %let vfound=0;

   %let i=1;

   %let subbusca="";

   %let vnxxlin="";

   %let vpna="";

   %let vtipo=0;

       %put ptelefono &ptelefono.;

         %let vpna = %substr(&ptelefono,%length(&ptelefono)-9,3); /* separa codigo de area */

         %put VPNA &vpna.;

         %let vnxxlin = %substr(&ptelefono,%length(&ptelefono)-6,&vmaxlen); /* separa numero telefono */

         %put VNXXLIN &vnxxlin.;

   %do %until ((&i eq &vmaxlen) | (&vfound eq 1));

         /*%do i=1 %to &vmaxlen;*/

             %put &vnxxlin. &i.;

               %let subbusca = %substr(&vnxxlin,1,&vmaxlen-(&i-1));

             %put subbusca &subbusca;

            /* data _null_;

    set QDATA.DATOS_TELEFONO_TIPO;

    where NIVEL = "1"

      AND   NIVEL_SUPERIOR = "3"

  AND   ID_REGION = "&subbusca."

                AND   ID_REGION_SUPERIOR = "&vpna.";

  run;*/

             proc sql noprint;

                  select (put(count(*), best32.))

                       into :vfound

                     from QDATA.DATOS_TELEFONO_TIPO AS DATOS_TELEFONO_TIPO

                     where DATOS_TELEFONO_TIPO.NIVEL = "1"

                     AND   DATOS_TELEFONO_TIPO.NIVEL_SUPERIOR = "3"

                     AND DATOS_TELEFONO_TIPO.ID_REGION = "&subbusca."

                     AND DATOS_TELEFONO_TIPO.ID_REGION_SUPERIOR = "&vpna.";

                  select tipo_telefono

                       into :vtipo

                     from QDATA.DATOS_TELEFONO_TIPO AS DATOS_TELEFONO_TIPO

                     where DATOS_TELEFONO_TIPO.NIVEL = "1"

                     AND   DATOS_TELEFONO_TIPO.NIVEL_SUPERIOR = "3"

                     AND DATOS_TELEFONO_TIPO.ID_REGION = "&subbusca."

                     AND DATOS_TELEFONO_TIPO.ID_REGION_SUPERIOR = "809";

              quit;

                  %let i = %eval(&i + 1);

                  %put VFound: &vfound. ;

                  %put Tipo Telefono Encontrado &vtipo.;

          

      %end;

   %put Tipo &vtipo.;

           /* Regresa el valor de tipo telefono */

%mend f_tipo_telefono;

  data osmel;

   length variable $ 30;

   if %f_tipo_telefono (8099864263) = '2' then variable='movil';

   if %f_tipo_telefono (8099864263) = '0' then variable='sin tipo';

run;

But when I run the program, it produces several errors

ptelefono 8099864263

VPNA 809

VNXXLIN 9864263

9864263 1

subbusca 9864263

NOTE: Line generated by the invoked macro "F_TIPO_TELEFONO".

1     proc sql noprint;                   select (put(count(*), best32.))                        into

           ---                                              -

           388                                              386

           76                                               200

                                                            76

1  ! :vfound                      from QDATA.DATOS_TELEFONO_TIPO AS DATOS_TELEFONO_TIPO

1  !        where

NOTE: Line generated by the invoked macro "F_TIPO_TELEFONO".

3                        select tipo_telefono                        into :vtipo

                                -------------

                                395

                                76

3  ! from QDATA.DATOS_TELEFONO_TIPO AS DATOS_TELEFONO_TIPO                      where

3  ! DATOS_TELEFONO_TIPO.NIVEL = "1"                      AND

NOTE: Line generated by the invoked macro "F_TIPO_TELEFONO".

5       DATOS_TELEFONO_TIPO.NIVEL_SUPERIOR = "3"                      AND

5  ! DATOS_TELEFONO_TIPO.ID_REGION = "&subbusca."                      AND

5  ! DATOS_TELEFONO_TIPO.ID_REGION_SUPERIOR = "809";                quit;

                                                                    ----

                                                                    180

VFound: 0

Tipo Telefono Encontrado 0

9864263 2

subbusca 986426

ERROR 388-185: Expecting an arithmetic operator.

ERROR 386-185: Expecting an arithmetic expression.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 395-185: Opening parenthesis for SELECT/WHEN expression is missing.

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

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.OSMEL may be incomplete.  When this step was stopped there were 0

         observations and 1 variables.

NOTE: DATA statement used (Total process time):

      real time           0.46 seconds

      cpu time            0.03 seconds

NOTE: No rows were selected.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.32 seconds

      cpu time            0.06 seconds

VFound:                                0

Tipo Telefono Encontrado 0

9864263 3

subbusca 98642

NOTE: No rows were selected.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.02 seconds

      cpu time            0.01 seconds

VFound:                                0

Tipo Telefono Encontrado 0

9864263 4

subbusca 9864

NOTE: No rows were selected.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

VFound:                                0

Tipo Telefono Encontrado 0

9864263 5

subbusca 986

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

VFound:                                1

Tipo Telefono Encontrado 2

Tipo 2

180: LINE and COLUMN cannot be determined.

NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL may allow recovery of the LINE and COLUMN where the

      error has occurred.

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

75      i

75 !     f %f_tipo_telefono (8099864263) = '0' then variable='sin tipo';

ptelefono 8099864263

VPNA 809

VNXXLIN 9864263

9864263 1

subbusca 9864263

75      if %f_tipo_telefono (8099864263) = '0' then variable='sin tipo';

        --

        180

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

NOTE: Line generated by the invoked macro "F_TIPO_TELEFONO".

1     proc sql noprint;                   select (put(count(*), best32.))                        into

                                          ------

                                          180

1  ! :vfound                      from QDATA.DATOS_TELEFONO_TIPO AS DATOS_TELEFONO_TIPO

1  !        where

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

NOTE: Line generated by the invoked macro "F_TIPO_TELEFONO".

3                        select tipo_telefono                        into :vtipo

                         ------

                         180

3  ! from QDATA.DATOS_TELEFONO_TIPO AS DATOS_TELEFONO_TIPO                      where

3  ! DATOS_TELEFONO_TIPO.NIVEL = "1"                      AND

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

VFound: 0

Tipo Telefono Encontrado 0

9864263 2

subbusca 986426

NOTE: No rows were selected.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

VFound:                                0

Tipo Telefono Encontrado 0

9864263 3

subbusca 98642

NOTE: No rows were selected.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

Is it not possible to run inline macro functions that use proc sql in the macro body?

Thanks in advance

VFound:                                0

Tipo Telefono Encontrado 0

9864263 4

subbusca 9864

NOTE: No rows were selected.

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

VFound:                                0

Tipo Telefono Encontrado 0

9864263 5

subbusca 986

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

VFound:                                1

Tipo Telefono Encontrado 2

Tipo 2

180: LINE and COLUMN cannot be determined.

NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL may allow recovery of the LINE and COLUMN where the

      error has occurred.

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

76   run;

Attachment

Accepted Solutions
Solution
‎10-25-2013 01:04 PM
Occasional Contributor
Posts: 15

Re: Macro Inline Function

Thanks Tom for your recommendation

I was looking for other techniques for lookup data and found this paper http://www2.sas.com/proceedings/forum2008/095-2008.pdf that talks about differents approaches for looking data using data step. One of them is hash objects, that I''ve never used before, and now it worked for me

This is the code I made

data tlfosmel;

  input TELEFONO $ 1-10;

datalines;

8099864263

8095447719

8099750762

8095406263

8297664263

8095633233

8499434370

;

run;

data w_datos_telefono_tipo;

   set QDATA.DATOS_TELEFONO_TIPO;

   subbuscar = cats(id_region_superior, id_region);

where nivel = "1" and nivel_superior = "3";

run;

data tlfosmel3;

vfound = 0;

i=1;

if 0 then set tlfosmel w_DATOS_TELEFONO_TIPO;

if _n_=1 then do;

     declare hash wh (dataset:'w_DATOS_TELEFONO_TIPO');

       wh.definekey ('subbuscar');

       wh.definedata('TIPO_TELEFONO');

       wh.definedone();

end;

set tlfosmel;

vpna = substr(TELEFONO,length(TELEFONO)-9,3);

vnxxlin = substr(TELEFONO,length(TELEFONO)-6,7);

  do  until ((i eq 11) | (vfound eq 1));

            subbusca = compress(substr(TELEFONO,1,10-(i-1)));

            put subbusca i;

            if wh.find(key:subbusca) = 0 then do

           vfound = 1;

               output;

               put 'Encontró';

            end;

            else do;

               put 'No Encontró';

            end;

            i+1;

           

  end;

 

  format tipo_telefono $tipo_telefono.;

  drop i id_region id_region_superior nivel region_desc localidad

       telefonica nivel_superior vfound subbuscar subbusca ;

run;

View solution in original post


All Replies
Super User
Posts: 5,099

Re: Macro Inline Function

SAS runs one DATA or PROC step at a time.  When you call your macro from a DATA step, it generates something like this:

data osmel;

length variable $ 30;

if proc sql noprint; select ...

Surely if you saw this code, you would expect to get an error.  That's the code that SAS sees when you call your macro in the middle of a DATA step.  The first step in solving this is to think about what you would like your program to look like.  It doesn't matter how the code is generated (whether it uses macro language or not).  What matters is the statements that SAS sees.

Good luck.

Occasional Contributor
Posts: 15

Re: Macro Inline Function

Thanks Astounding

I was trying to replicate a function like Oracle.  As I understand you told me, it's impossible to read a table (using a proc sql or data step) from a macro inline function, am I right?

Thanks for your time and your answer

Occasional Contributor
Posts: 6

Re: Macro Inline Function

You could do it by making liberal use of %SYSFUNC on functions such as FOPEN, FCLOSE, FREAD, and FGET.  Don't expect much in the way of performance if the tables are of any significant size.  The Macro language was made for writing code, not for data processing.

One additional note - when you want to output a macro variable value from a macro to the surrounding code, you just reference the macro variable in the macro code without a %PUT or any other modifier.  Here is an example where the macro is called to return the number of observations into the code - http://www2.sas.com/proceedings/sugi26/p095-26.pdf

Super User
Posts: 5,099

Re: Macro Inline Function

The way you word it, it's true.  You can't use PROC SQL or a DATA step inline.  But SAS does contain other tools, as some of the other posters have indicated.

In your case, it seems like it might be viable to run PROC SQL first, without even beginning a DATA step.  Then use macro language to extract and use the pieces that come out of the PROC SQL.  We might have to know a little more about your objective to come up with the best approach.

Occasional Contributor
Posts: 15

Re: Macro Inline Function

I'm usign now SAS 9.1.3, but we are planning to upgrade to SAS 9.3 or 9.4

the goal of routing is to identify if a phone number is a movil or a wired phone.  This will be done querying the dataset attached to original post.  The macro inline function will be used in proc sql that query our customer telephones database, prior to send SMS, so it's important to us to identify that

Super User
Posts: 5,099

Re: Macro Inline Function

Here are a few key questions that would help in designing a good approach.

How often does the pool of phone numbers change?  (And is it ever possible that a phone number switches between being wired and being mobile?)

Does your look-up system have to be "always available", or do you have a window of time when your look-up system can be offline while it is updated?

When you look up a phone number, are you looking up a single phone number or are you looking up a set of phone numbers?  (If it's a set, approximately how many are in a set?)  How important is the speed of search?

Occasional Contributor
Posts: 6

Re: Macro Inline Function

Macro programs are programs that write SAS code which is then compiled.  This SAS code is treated no differently than the code you type.  So if you start a PROC SQL and call a macro inside that PROC and the macro contains/runs a DATA step, it is like typing in part of a PROC SQL and then suddenly typing in a DATA step.  The change from PROC SQL to DATA step ends the PROC SQL.  This is called a "step boundary".  Final compilation and execution occur at step boundaries.  See http://support.sas.com/kb/40/887.html for more information on step boundaries.

So if you want to call a macro from within a PROC or DATA step without exiting that PROC or DATA step, the macro code cannot have any PROC or DATA step inside it.  If it does, you will create a step boundary that will end the step you had started prior to calling the macro.

Occasional Contributor
Posts: 15

Re: Macro Inline Function

Thanks

Respected Advisor
Posts: 3,124

Re: Macro Inline Function

This is off topic, but you maybe interested in the following new functions that SAS is offering after 9.3M2:

1. to call and execute code before current data step ends, dosubl()

2. Check out Proc DS2

Haikuo

PROC Star
Posts: 7,366

Re: Macro Inline Function

You may want to learn about proc fcmp (see, e.g., http://www.nesug.org/proceedings/nesug07/bb/bb14.pdf ).

I haven't reviewed your specs, but it is quite possible that you can use that proc to create the function you want/need.

Occasional Contributor
Posts: 15

Re: Macro Inline Function

Thanks to everyone, each of your answers helped me to get the solution

I found this paper Executing a PROC from a DATA Step (http://support.sas.com/resources/papers/proceedings12/227-2012.pdf) and using PROC FCMP, I could write an inline function.

The final program is the following

%macro m_tipo_telefono;

   %let vfound=0;

   %let i=1;

   %let subbusca="";

   %let vnxxlin="";

   %let vpna="";

   %put ptelefono &ptelefono;

   %let vpna = %substr(&ptelefono,%length(&ptelefono)-9,3); /* separa codigo de area */

    %put VPNA &vpna.;

   %let vnxxlin = %substr(&ptelefono,%length(&ptelefono)-6,7); /* separa numero telefono */

%put VNXXLIN &vnxxlin.;

   %do %until ((&i eq 7) | (&vfound eq 1));

     

       %let subbusca = %substr(&vnxxlin,1,7-(&i-1));

%put subbusca &subbusca;

proc sql noprint;

select (put(count(*), best32.))

into :vfound

from QDATA.DATOS_TELEFONO_TIPO AS DATOS_TELEFONO_TIPO

where DATOS_TELEFONO_TIPO.NIVEL = "1"

AND DATOS_TELEFONO_TIPO.NIVEL_SUPERIOR = "3"

AND DATOS_TELEFONO_TIPO.ID_REGION = "&subbusca."

AND DATOS_TELEFONO_TIPO.ID_REGION_SUPERIOR = "&vpna.";

                  select tipo_telefono

into :vtipo

from QDATA.DATOS_TELEFONO_TIPO AS DATOS_TELEFONO_TIPO

where DATOS_TELEFONO_TIPO.NIVEL = "1"

AND   DATOS_TELEFONO_TIPO.NIVEL_SUPERIOR = "3"

AND DATOS_TELEFONO_TIPO.ID_REGION = "&subbusca."

AND DATOS_TELEFONO_TIPO.ID_REGION_SUPERIOR = "809";

              quit;

                  %let i = %eval(&i + 1);

      %end;

/* Regresa el valor de tipo telefono */

%mend m_tipo_telefono;

proc fcmp outlib=sasuser.funcs.sql;

function return_tipotel (ptelefono) $;

length vtipo $ 10;

rc = run_macro('m_tipo_telefono', ptelefono, vtipo);

return (vtipo);

endsub;

run;

libname qdata "C:\SAS\qdata";

options cmplib = sasuser.funcs;

data osmel;

vals = return_tipotel(8099864263);

run;

I executed in SAS 9.2 and it worked fine. I will execute it in SAS 9.1.3

PROC Star
Posts: 7,366

Re: Macro Inline Function

proc fcmp only became available with 9.2

Occasional Contributor
Posts: 15

Re: Macro Inline Function

PROC FCMP exists in SAS 9.1.3 but run_macro facility is not available

Thanks

PROC Star
Posts: 7,366

Re: Macro Inline Function

I sit corrected!  I just found the following comment in a paper (http://support.sas.com/resources/papers/proceedings11/083-2011.pdf ):

Since SAS 9, the FCMP procedure has been available for users to create SAS functions and subroutines that can be

used by other SAS procedures. In SAS 9.1.3, the user function defined by PROC FCMP can only be used in the

following procedures: CALIS, COMPILE, DISTANCE, GA, GENMOD, MODEL, NLIN, NLMIXED, NLP, PHREG,

RISKDIMENSIONS, ROBUSTREG, SIMILAR, SYLK. Since SAS 9.2, these user functions can be called from a DATA

step like other SAS functions. Therefore, the functions and subroutines defined by PROC FCMP can be independent

from the main program, which makes it different from SAS macro and IML. This feature also greatly enhanced the

ability for SAS programs to be read and maintained.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 1591 views
  • 10 likes
  • 6 in conversation