DATA Step, Macro, Functions and more

Missing Value in variable as macro

Reply
Contributor
Posts: 44

Missing Value in variable as macro

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

Trusted Advisor
Posts: 1,931

Re: Missing Value in variable as macro

Posted in reply to yashpande

Is your question:

  1. 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)
  2. 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)
  3. 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")
Super User
Posts: 19,844

Re: Missing Value in variable as macro

Posted in reply to yashpande

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

Trusted Advisor
Posts: 1,931

Re: Missing Value in variable as macro

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;

Contributor
Posts: 44

Re: Missing Value in variable as macro

Posted in reply to PaigeMiller

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 ?

Super User
Super User
Posts: 7,070

Re: Missing Value in variable as macro

Posted in reply to yashpande

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.

 

Trusted Advisor
Posts: 1,931

Re: Missing Value in variable as macro

Posted in reply to yashpande


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>

Frequent Contributor
Frequent Contributor
Posts: 83

Re: Missing Value in variable as macro

Posted in reply to yashpande


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

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

var _numeric_;

run;



Frequent Contributor
Frequent Contributor
Posts: 83

Re: Missing Value in variable as macro

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;

 


Ask a Question
Discussion stats
  • 8 replies
  • 1170 views
  • 0 likes
  • 5 in conversation