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
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...?
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
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
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
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
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.
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
*/
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: