Hi All,
I have a dataset Have which may or may not contain data. I need to basically create one macro variable which will have value zero if its missing.
Data have ;
Input var1 var2;
Cards;
1 a
. B
;
Run;
My macro is
%macro m1;
Proc sql;
Select var1 into :var1 where var2='B'
;
Quit;
%let var1_new=%eval(&var1 +1);
%mend;
%m1;
So basically I want var1_new to be resolved to 1
Has anyone faced similar problem. Any ideas are welcome
Is your question:
Use the sum function instead, once you fix the code before. 2 additional bugs above...
1. Input - didn't identify character variable
2. Proc SQL - no from table
3. Use %sysfunc(sum(&var1, 1)) instead of eval
Data have ;
Input var1 var2 $;
Cards;
1 a
. B
;
Run;
Proc sql;
Select var1 into :var1
from have
where var2='B';
Quit;
%let var1_new=%sysfunc(sum(&var1, 1));
%put &var1_new.;
Fareeza Khurshed wrote:
Use the sum function instead, once you fix the code before. 2 additional bugs above...
1. Input - didn't identify character variable
2. Proc SQL - no from table
3. Use %sysfunc(sum(&var1, 1)) instead of eval
Data have ;
Input var1 var2 $;
Cards;
1 a
. B
;
Run;
Proc sql;
Select var1 into :var1
from have
where var2='B';
Quit;
%let var1_new=%sysfunc(sum(&var1, 1));
%put &var1_new.;
How about this?
Data have ;
Input var1 var2 $;
if var2='B' then call symputx('var1_new',not missing(var1));
Cards;
1 a
. B
;
Run;
Hi Page ,I want to select the latest value. If its missing then I should get zero. Will above call symput work in such case ? Let me try it and get back to you. As for sql and sum concerned I think sum won't work because say if I have 2 non missing values then it will sym up and result will be wrong. I want latest value. If there is no latest then it should be treated as zero.
Will coalesce work in such cases ?
Let's assume that the problem is to find the LAST value for a numeric variable when using a WHERE clause. Further, if the last value is missing or if there are no observations in the input dataset then return 0 as the result.
In this example I use WANT as the input dataset, VAR1 as the name of the numeric variable and LAST_VAR1 as the name of the macro variable to contain the result. For my WHERE condition I will use VAR2='B' .
%let last_var1=0;
data _null_;
set have end=eof ;
where var2='B';
if eof then call symputx('last_var1',sum(0,var1));
run;
This will NOT handle the case where the input dataset does not exist. In that case you will get an ERROR from SAS for attempting to reference a dataset that doesn't exist.
Hi Page ,I want to select the latest value. If its missing then I should get zero. Will above call symput work in such case ? Let me try it and get back to you. As for sql and sum concerned I think sum won't work because say if I have 2 non missing values then it will sym up and result will be wrong. I want latest value. If there is no latest then it should be treated as zero.
Realizing that it is possible that English is not your native language, do you mean "last value" or "latest value"? "Last value" makes sense assuming the data is sorted in the proper order, "latest value" does not make sense to me here.
Regarding this criterion of using the "Last" or "Latest" value, it sure would have been nice if you had stated this criterion right at the start instead of letting us guess you wanted var2='B'
So, assuming the data is sorted properly by var2, this code would work
Data have ;
Input var1 var2 $;
Cards;
1 a
. B
;
Run;
data _null_;
set have end=eof;
if eof then call symputx('var1_new',not missing(var1));
run;
<humor> where data _null_; is an actual SAS command and does not refer to the individual in this forum who uses the name data _null_; </humor>
the following procedure will replace all numeric variable values with zero.
proc stdize data= have reponly MISSING=0 out=want;
var _numeric_;
run;
I mean all non missing
the following procedure will replace all missing numeric variable values with zero.
proc stdize data= have reponly MISSING=0 out=want;
var _numeric_;
run;
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.