Creating macro variables in an SQL and pass back

[ThomasH]

Hi all,

I am trying to call a PROC SQL in a macro, where the SQL creates 2 variables via "INTO :" and then continue to use it in that calling macro. But somehow, this doesn't work as the 2 variables are always "0" in the following code.

Anybody got an idea? Appreciate every help!

%let id = 0;
%let version = 0;

%MACRO CoolLookup(Zahl);
proc sql;
select ID, VERSION into :ID, :VERSION
from myLib.mytable
where LOOK_HERE = &Zahl. AND LOOK_THERE = 'T';
quit;

%MEND CoolLookup;

%MACRO start;
data test_port;

CALL EXECUTE('%CoolLookup(601)');

test_601_ID = &ID.;
test_601_VERSION = &VERSION.;

run;

proc print data = test_port;
run;

%MEND start;

%start;

Thanks and best regards,
Thomas

[sbb]

You are attempting to execute a macro from within a DATA step execution. With CALL EXECUTE, the macro you have coded with not be executed until the DATA step has completed. It's unclear why you have the CALL EXECUTE coded anyway - simply invoke the PROC SQL macro ahead of the DATA step.

Scott Barry
SBBWorks, Inc.

[LinusH]

The CALL EXECUTE will be executed after the calling data step has finished, so in your case the assignment will be performed before the SQL that will calculate the value for the macro variable. This is because a SQL cannot run within a data step...

/Linus

[GertNissen]

Hi ThomasH

Its unclear to me what you are trying to do, but it looks like you are running the wrong way (trying the write SAS code based on experience from another programming language - just guessing).

Can you explain what you are trying - I think another approach might be better.

Perhaps you could provide us with an example of before/after data - Then we can try to help you building some new SAS code.

Are you doing a lookup, left join, transpose or ..... ?

[Fisher]

I agree with Zineg that Thomash tried to call that macro to update the two variable values in 'real time' just like other programming languages such a java, .net etc.

My question is: whether there is some way to do this way in SAS since CALL EXECUTE can't do that?

Here is the case:

I like to loop each obs in a data step, for some obs, I like to create a new variable and call a macro to do some checking or calculations and then immediately return a value which is assigned to the the new variable.

I know this is very easy in other programming language, but how to achieve it in SAS or whether it is possible in SAS?

Thanks.

[CurtisMack]

You should look into PROV FCMP if you are on a new enough version of SAS. It defines a function (not a macro) that can be called within a Data Step.

If not this, then look into the OPEN, FETCH, GETVARC, GETVARN functions. They can be used within a Data Step to open another dataset and get needed information.

[Fisher]

Thanks Curtis.
This is a very good new feature for version 9.2. But seems users can only define functions, and there is no way to embed a proc sql or data step within this new procedure.
What I really want to do is to check another dataset using either proc sql or data step and return some value while looping through each observation of a dataset in a data step.

As for the OPEN, FETCH etc. functions, I am not sure whether they apply to my situation as I don't try to fetch an obs from another dataset but like to query for the largest values based on some condition.

[Peter_C]

>
> As for the OPEN, FETCH etc. functions, I am not sure
> whether they apply to my situation as I don't try to
> fetch an obs from another dataset but like to query
> for the largest values based on some condition.

if you have SAS.AF available, you might find it the language environment you seek.
* It allows you to submit blocks of base SAS code and await results
* among SAS/SCL functions that become available when you use SAS/AF is VARSTAT() which will "immediately" return the stats you request for a SAS dataset you have open
see http://support.sas.com/documentation/cdl/en/sclref/59578/HTML/default/a000146063.htm

lots of potential
peterC

[chang_y_chung_hotmail_com]

@Fisher or @ThomasH:



If you insist on doing it your way, then you can. In the data step, max, below, you are calling a function maxOf() for each name stored in another dataset, var. For each function call, a proc sql step is run to calculate and to return the maximum value of the given variable in the given dataset.

   %macro maxOf;

      %global var data r;

      %let var = %sysfunc(dequote(&var));

      %let data = %sysfunc(dequote(&data));

      proc sql noprint;

         select left(putn(max(&var),"best32.")) into :r

         from &data;

      quit;

   %mend maxOf;

   proc fcmp outlib=work.func.test;

      function maxOf(var $, data $);

         rc = run_macro('maxOf', var, data, r);

         if rc = 0 then return(r);

         else return(.);

      endsub;

   quit;

   %let cmplib = %sysfunc(getoption(cmplib));

   options cmplib = (&cmplib work.func);

      data var;

         do name = "weight", "height", "age";

            output;

         end;

      run;

      data max;

         set var;

         maxVal = maxOf(name, "sashelp.class"); 

      run;

      /* check */

      proc print data=max;

      run;

      /* on lst           

                       max

      Obs    name      Val

       1     weight    150

       2     height     72

       3     age        16

      */

   options cmplib = (&cmplib);

[Fisher]

hahaha..... I don't think this logic is odd at all. Does it not make sense to tell data step something like 'hey, guy! hold on please. Let me finish something else before keep going'?! 🙂

Anyway, thanks for the code example. I found SCL functions OPEN, FETCH etc. can help me to implement this logic in a simpler way.

By the way, anybody knows whether there is any SCL functions to check and return the total rows of the dataset after OPEN an dataset? I checked the SCL document and found nothing.

Thanks all again.

[ballardw]

Look at documentation for FINFO function. The information about external files depends on your OS and file system. There should be an example that will let find out all of the file information the system will return.

[CurtisMack]

In a FCMP function, you can call a macro that uses a data step or procedures. They will execute in line just like you are looking for.

Be carefull using FINFO if you go that route. I got bit by the fact that it will count rows that have been deleted from the table. SAS will sometimes mark a row as deleted, but FINFO will still count them.

[CurtisMack]

I think this example is close to what you are looking for:

data junk1;
x = 1; output;
x = 2; output;
run;

data junk2;
x = 3; output;
x = 4; output;
run;

data datasets;
Thisdataset = 'junk1'; output;
Thisdataset = 'junk2'; output;
run;

%macro getsum;
%put Here is &datasetIN;
%let datasetIN = %sysfunc(dequote(&datasetIN));
proc sql ;
select sum(x)
into :thesum
from &datasetIN;
quit;
%mend;

proc fcmp outlib = sasuser.ds.functions;
subroutine getsum(datasetIN $,thesum);
outargs thesum;
rc = run_macro('getsum',datasetIN,thesum);
endsub;
run;
option cmplib = (sasuser.ds);

data junk3;
set datasets;
length thesum 8;
call getsum(Thisdataset,thesum);
run;

[Fisher]

Thanks Curtis. It is a simple and clear code example. I have learned a lot from this posting. Thanks All SAS guru here.

[Cynthia_sas]

Hi:
Like everybody else said, I'm not sure what you need to do with this code. But, as others have noted, your CALL EXECUTE invocation of the macro program merely puts statements into the "stack" which will begin to execute AFTER the data step program is over.

Therefore, you have a timing problem. You seem to want to assign the value of &ID and &VERSION -inside- the DATA step -- which will simply not work the way you expect. When the DATA step is executing, the values for &ID and &VERSION are 0 because they will not have any other value until AFTER the DATA step finishes executing and the statements created by the macro program start to execute.

In the interests of helping you clarify what it is you might want to do, I have created some data from SASHELP.CLASS with variables similar to those you used in your program. One possible version of a macro program, followed by some data steps that use the macro variables is shown below.

Perhaps if you can expand a bit on WHAT you need to do, others can offer more useful suggestions. BTW, I consider keyword parameters to be a "best practice" when writing macro programs, so I changed the macro to use keyword parameters.

cynthia
[pre]
%let id = 0;
%let version = 0;

** make some data;
** needed unique value for Look_Here, so used name;
** instead of numeric variable;
data mytable;
set sashelp.class;
id = _n_;
version = age;
Look_Here = name;
Look_There = upcase(substr(name,1,1));
run;

** define macro program;
** changed macro so that &ID and &VERSION were ;
** GLOBAL instead of LOCAL to the macro scope;
** also changed the parameters to keyword parameters;
%MACRO CoolLookup(want=, lkt=);
%global id version;

proc sql;
select ID, VERSION into :ID, :VERSION
from mytable
where LOOK_HERE = "&want" AND LOOK_THERE = "&lkt";
quit;

%put inside macro:;
%put id= &id;
%put version= &version;

%MEND CoolLookup;

** Invoke Macro for Obs of interest;
%CoolLookup(want=Alfred,lkt=A);

** now use ID= and version = in a WHERE statement;
** to prove that macro vars were set correctly;
data proof;
set mytable;
where id = &id and version = &version;
test_ID = &ID.;
test_VERSION = &VERSION.;
run;

proc print data = proof;
title "My Report for ID=%sysfunc(compress(&id)) and Version=%sysfunc(compress(&version))";
run;
title;

** create table with ONLY id and version;
data test_port;
test_601_ID = &ID.;
test_601_VERSION = &VERSION.;
run;

proc print data = test_port;
title "Only 2 vars in table";
run;
title;
[/pre]