Hey people,
I'm new to SAS and I'm sorry if this problem has a very simple solution that I'm to blind to see.
I want to define a Macro variable called "write_forward(varnames= , typelist=)" and the goal is to enter variables and the type of the variables to replace missing values of the variables with their last observation. Therefore I wrote:
%macro write_forward (varnames, typelist);
data forward;
merge sorted_spread (in=a)
sorted_transaction (in=b);
by time;
retain _&varnames.;
if &varnames. = &typelist.
then &varnames. = _&varnames.;
else _&varnames. = &varnames.;
drop _&varnames.;
run;
%mend;
%write_forward (varnames= ask
,typelist= .);
And it works fine if I only enter one variable. The problem is that as soon as I enter more than one variable in the form of:
%write_forward(varnames= ask bid
,typelist= . .)
SAS takes this as a line and enters "ask bid" as varnames instead of entering "ask" and then do it again for the next element of the line "bid".
I searched quite a while for a solution but found nothing that explains my exact problem. I hope you can help me and if there are any necessary informations missing please just tell me and I will post them.
So you need to repeat these statements
retain _ask;
if ask ="."
then ask = _ask;
else _ask = ask;
drop _ask;
for a series of variables.
Step 1: replace explicit varnames with macro variable, and set the macrovar
%let var=ask;
retain _&var.;
if &var. ="."
then &var. = _&var.;
else _&var. = &var.;
drop _&var.;
To iterate through your variable list, do
%do i = 1 %to %sysfunc(countw(&varnames.));
%let var = %scan(&varnames.,&i.);
/* your code to repeat */
%end;
Here's the problem. When you write a macro, and then run it, it must produce valid legal working SAS code. Your macro does not do this.
So
if &varnames. = &typelist.
becomes
if ask bid = . .
when you run the macro, which is not legal valid working SAS code.
You probably want a loop of some sort, but honestly, your logic in the macro doesn't make any sense to me. Specifically this part:
then &varnames. = _&varnames.;
else _&varnames. = &varnames.;
So, I couldn't even help you write a macro with a loop at this time. Please write some code without macros and without macro variables that works and does what you want it to do (you need to test it to make sure it works and does what you want, do not skip this step) so we can see what you are really trying to do.
So you need to repeat these statements
retain _ask;
if ask ="."
then ask = _ask;
else _ask = ask;
drop _ask;
for a series of variables.
Step 1: replace explicit varnames with macro variable, and set the macrovar
%let var=ask;
retain _&var.;
if &var. ="."
then &var. = _&var.;
else _&var. = &var.;
drop _&var.;
To iterate through your variable list, do
%do i = 1 %to %sysfunc(countw(&varnames.));
%let var = %scan(&varnames.,&i.);
/* your code to repeat */
%end;
While @Kurt_Bremser is pretty close, the macro won't work on a mix of character and numeric variables without additional complications. If you have a character variable, then &var='.' will work, but it won't work for numeric variables. So, are you really in the situation where one variable in &varnames could be character and another variable in &varnames could be numeric?
Thanks a lot for your answer. First it didnt work cause I put the do statement before the data step but found the solution. So you're my todays hero KurtBremser.
There is an issue with the code sample you provided for a single variable. If the variable is numeric then the test condition is wrong because it is comparing a numeric value to a character value. If the variable is character then you will make the new retained variable as numeric instead of as character. That is because the first place it is used (first place where SAS needs to make a decision on the type, the RETAIN doesn' t need to know the type) is on the right side of an assignment statement, so SAS will assume the new variable is numeric. This is probably why the macro is designed to take in TWO lists, one with the names and one with the types.
You can fix that by changing the code just a little bit. (Code formatting note: do not split a single statement into two lines without at least adding some indentation). By using the MISSING() function you eliminate the need to know the type in advance. By reversing the test condition you can have the new variable first appear on the LEFT side of the assignment statement so that SAS will create it to match the type of the value on the right side of the assignment statement. Note that since the RETAIN statement, like the DROP statement, is not executable there is no need to have it first.
if not missing(ask) then _ask = ask;
else ask=_ask;
retain _ask;
drop _ask;
So now your macro does not even need the TYPELIST parameter. And there is no need to include the rest of the data step in the macro, just the lines of data step code that you want to generate. Just make sure to call the macro inside a data step so that the code is generated in a place where it makes sense. You might want to write a note to the log if anyone tries to use the TYPELIST parameter.
%macro write_forward (varnames, typelist);
%local i var ;
%if %length(&typelist) %then %put NOTE: Ignoring TYPELIST input as it is not needed. &=typelist;
%do i=1 %to %sysfunc(countw(&varnames,%str( )));
%let var=%scan(&varnames,%str( ));
if not missing(&var ) then _&var= &var;
else &var=_&var;
retain _&var;
drop _&var;
%end;
%mend write_forward;
And you could use it like this to deal with 4 variables.
data forward;
merge sorted_spread (in=a)
sorted_transaction (in=b)
;
by time;
%write_forward (varnames= ask a b c)
run;
Hi @MonsterGnom
Could you please try the following method to impute missing value using LOCF method?
No need to use a macroprogram.
data have;
merge sorted_spread (in=a) sorted_transaction (in=b);
by time;
id=1; /*identifier to group all observations together*/
run;
data forward (drop=id);
update have(obs=0) have;
by id;
output;
run;
Sounds like this is homework then.
@Kurt_Bremsers given you all the tools you need to solve this in his most recent answer.
You can also reference the macro library appendix for sample code on creating loops to loop through a list if you want or you can look into call execute to run your macro multiple times for different variables.
I prefer the second approach as I find it much easier to debug.
UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/
Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md
Examples of common macro usage - macro appendix
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...
@MonsterGnom wrote:
Thanks for your answer. I would very much like to do it without a macro that is not necessary but as i wrote in my reply above I'm forced to do so as well as I'm forced to use a retain statement to write it forward in the beginning.
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 25. 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.