11-16-2012 03:14 AM
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.
proc sql noprint;
select group into :&myMVar
where occupation = "&beruf";
set work.contract_psm03 (obs=10);
* occ_v = resolve('%Get_data('||PARTNER_VN_MBERUF||')');
occ_v = "&varname";
I hope somone could help me.
11-16-2012 05:33 AM
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...?
11-16-2012 06:40 AM
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
11-19-2012 02:39 AM
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.
11-19-2012 05:52 AM
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'
Rename Occupation = start
group = label
proc format cntlin = occ_fmt ;
data test ;
set temp.contract_psm03 ;
group = input (PARTNER_VN_MBERUF, ?? occcode.) ;
The ?? in the input function returns a missing value without creating an error if there is no match.
Richard in Oz
11-20-2012 03:50 AM
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.
11-19-2012 11:32 AM
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:
set occucpationlist2 (keep=occupation group);
call symputx(occupation, group);
The retrieval brings in the right one:
set work.contract_psm03 (obs=10);
length occ_v $ 50 /* or whatever seems reasonable */;
occ_v = symget(occupation);
Without a LENGTH statement, SYMGET would define OCC_V as being 200 characters long.
... 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.
11-19-2012 10:38 AM
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");
ok = 0;
if h.find() = ok then return(age);
age = age(name);
put name= age=;
11-19-2012 11:08 AM
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: