topic Re: Missing Value in variable as macro in SAS Programming

Missing Value in variable as macro

yashpande — Fri, 08 Nov 2013 17:34:27 GMT

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

Re: Missing Value in variable as macro

PaigeMiller — Fri, 08 Nov 2013 17:43:40 GMT

Is your question:

Has anyone faced similar problem? (that's what you said, that's the only question I can find in your post, in which case I think the answer is YES)
How to achieve this for this exact sitaution where you are testing for var2='B'? (in which case I think the answer is that you just did it)
How to achieve this in general? (in which case I think you need to be more specific about what general situations you want to find and what you mean by "zero if its missing")

Re: Missing Value in variable as macro

Reeza — Fri, 08 Nov 2013 17:45:58 GMT

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.;

Re: Missing Value in variable as macro

PaigeMiller — Fri, 08 Nov 2013 17:50:34 GMT

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;

Re: Missing Value in variable as macro

yashpande — Sat, 09 Nov 2013 06:22:01 GMT

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 ?

Re: Missing Value in variable as macro

Tom — Sat, 09 Nov 2013 22:03:02 GMT

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.

Re: Missing Value in variable as macro

PaigeMiller — Mon, 11 Nov 2013 14:20:26 GMT

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>

Re: Missing Value in variable as macro

Mit — Wed, 13 Nov 2013 04:55:54 GMT

the following procedure will replace all numeric variable values with zero.

proc stdize data= have reponly MISSING=0 out=want;

var _numeric_;

run;

Re: Missing Value in variable as macro

Mit — Mon, 18 Nov 2013 01:58:31 GMT

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;