Macro within data step

[chrwag]

Hallo,

What I am trying to do is to process a data step that executes a macro. The macro should select a value in a data set with a parameter based on a value in the current observation. When I submit the following code it seems to execute the macro and it return a value. But the value is a value based on the paramter of the last observation.

%MACRO Get_data(beruf,myMVar=);

     %GLOBAL &myMVar;

     proc sql noprint;

     select group into :&myMVar

     from occuptaionlist2

     where occupation = "&beruf";

     quit;

%MEND Get_data;

data test;

    set work.contract_psm03 (obs=10);

*    occ_v = resolve('%Get_data('||PARTNER_VN_MBERUF||')'); 

    call execute('%Get_data('||PARTNER_VN_MBERUF||',myMVar=varname)');

    occ_v = "&varname";

run;

I hope somone could help me.

Christoph

[LinusH]

As a basic rule, macro code replaces the macro call, therefore, the generated syntax from the macro must be executable from the calling point.

In your case, the code generated from the macro must fit in your data step.

Call Execute on the other hand, lets you execute any code that you want, but the cost is that the code can't execute within data step execution, it'e executed after the data step has ended.

In your case, the macro generates a global macro variable. Even if you macro is called for each row of contract_psm03, the macro variable varname (which is hard coded in the call execute) will be overwritten until the last call execute.

What is your final cause? Maybe there is an easier way to accomplish what you are trying to do...?

[RichardinOz]

What you seem to want to do is to run some SQL code (in the macro) in the middle of a data step and insert the returned value in a column of the output datastep table.  Newsflash: SAS has yet to catch up with you on this idea. 

You are going to have to tackle this problem somewhat differently, maybe as a series of (possibly nested) SQL queries. Give us some data to work with, and the output you want to produce (10 obs is fine).

Richard in Oz

[chrwag]

What you seem to want to do is to run some SQL code (in the macro) in the middle of a data step and insert the returned value in a column of the output datastep table. 

This was exactly what I was trying to do. I wrote functions like this in sql environments so I thought it could be possible in SAS.

You are going to have to tackle this problem somewhat differently,

I know that I could write a proc sql step with a join or a match merge statement to solve my problem. But a function like this seems to be a nice way to retrieve values.

Give us some data to work with, and the output you want to produce (10 obs is fine).

I uploaded some data. Based on "PARTNER_VN_MBERUF" in contract_psm03 I would like to select the matching value of group in occuptaionlist2. The result is in the test data set.

Christoph

[RichardinOz]

Christof

Sorry if I was a bit harsh.

There is a way to create functions in SAS, it is Proc FCMP (function compiler) but I think that's a more complex solution than you need.  Looking at your data, a simple SQL merge or left join would suffice, even for large tables.  You can also do a datastep merge but this requires both tables to be sorted by the common column.  Someone here may come up with a hash solution: I prefer the less convoluted approach.

Another technique which might be closer to what you are trying to achieve is to create a format from the occupation data and apply the format to your contract table.  In this case you need an informat because the returned value is numeric.  To create the informat you have to give it a name and specify a type.

libname temp 'C:\Users\Public\Temp' ;

data occ_fmt ;

     Set   temp.occuptaionlist2 ;

     Retain    fmtname    'occcode'

            type    'I'

            ;

    Rename    Occupation    =    start

            group        =    label

            ;

run ;

proc format cntlin    = occ_fmt ;

quit ;

data test ;

    set     temp.contract_psm03 ;

    group    =    input (PARTNER_VN_MBERUF, ?? occcode.) ;

run ;

The ?? in the input function returns a missing value without creating an error if there is no match.

Richard in Oz

[chrwag]

Yesterday I tested Richard's way with creating a format. In my program I had a step where I joined the occupation table 10 times and it took about 40 Minutes. Now it is done in 2 Minutes. Pefect. Thanks a lot.

Christoph

[Astounding]

It seems that each value of OCCUPATION is a valid SAS name, since you are using it as the name of a macro variable.  In that case, here is another alternative.  The set-up creates a set of macro variables:

data _null_;

   set occucpationlist2 (keep=occupation group);

   call symputx(occupation, group);

run;

The retrieval brings in the right one:

data test;

   set work.contract_psm03 (obs=10);

   length occ_v $ 50 /* or whatever seems reasonable */;

   occ_v = symget(occupation);

run;

Without a LENGTH statement, SYMGET would define OCC_V as being 200 characters long.

Good luck.

... or perhaps that should be:

occ_v = symget(partner_vn_mberuf);

... or perhaps I should not have posted at all.  It looks like my assumption that OCCUPATION takes on valid SAS names is not necessarily true.  Oops.  If it is true, this would work but otherwise you have plenty of approaches to play with.

[chang_y_chung_hotmail_com]

I disagree with RichardinOz since proc fcmp solution is straight-forward. See below. This is efficient as well, because the hash is loaded only once. hth.

  proc fcmp outlib=work.funcs.age;
    function age(name $);
       declare hash h(dataset:"sashelp.class");
       rc=h.definedata("age");
       rc=h.definekey("name");
       rc=h.definedone();
       ok = 0;
       if h.find() = ok then return(age);
       else return(.);
    endsub;
  quit;
 
  options cmplib=work.funcs;
 
  data _null_;
     set sashelp.class(keep=name);
     age = age(name);
     put name= age=;
  run;
  /*
  Name=Alfred age=14
  Name=Alice age=13
...
  Name=William age=15
  */

[Reeza]

Rather than use proc fcmp why not use a proc format via the lookup table method. Using things that are already available seems the best option to me.

See example 4 in this paper:

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000761932.htm