Using macros to code for missing data

[catch18]

Hi,

I'm trying to set the zeros in my dataset to missing, so SAS doesn't state it as my minimum value when I run proc means etc.

I have about 40 variables so I've used macros as follows:

%let xlist= Al_LFFQ Cal_LFFQ Carb_LFFQ Chol_LFFQ Cu_LFFQ E_LFFQ Fat_LFFQ Fe_LFFQ Fibre_LFFQ
Fola_LFFQ Fru_LFFQ Glu_LFFQ I_LFFQ K_LFFQ Lact_FFQ MUFA_LFFQ Mg_LFFQ Mn_LFFQ N_LFFQ NiacE_LFFQ
Niac_LFFQ PUFA_LFFQ P_LFFQ Prot_LFFQ Ret_LFFQ Ribof_LFFQ SFA_LFFQ Se_LFFQ Star_LFFQ Suc_LFFQ
TVitA_LFFQ Thia_LFFQ VitB12_LFFQ VitB6_LFFQ VitC_LFFQ VitD_LFFQ VitE_LFFQ Zn_LFFQ b_C_LFFQ;

/*coding 0 values to missing*/
data work.drake;
  set Theresa.drake;
   if &xlist=0 then &xlist=.;
  run;

I get an error code as below when I run it." Could you please help? Thanks

/*coding 0 values to missing*/

327 data work.drake;

328 set Theresa.drake;

329 if &xlist=0 then &xlist=.;

NOTE: Line generated by the macro variable "XLIST".

1 Al_LFFQ Cal_LFFQ Carb_LFFQ Chol_LFFQ Cu_LFFQ E_LFFQ Fat_LFFQ Fe_LFFQ Fibre_LFFQ Fola_LFFQ

--------

388

76

1 ! Fru_LFFQ Glu_LFFQ I_LFFQ K_LFFQ Lact_FFQ MUFA_LFFQ Mg_LFFQ Mn_LFFQ N_LFFQ NiacE_LFFQ

1 ! Niac_LFFQ PUFA_LFFQ P_LFFQ Prot_LFFQ Ret_LFFQ Ribof_LFFQ SFA_LFFQ Se_LFFQ

ERROR 388-185: Expecting an arithmetic operator.

ERROR 76-322: Syntax error, statement will b

[hlnquynh]

I think using array will be better.

data work.drake;
set Theresa.drake;
array missng (*) Al_LFFQ Cal_LFFQ Carb_LFFQ Chol_LFFQ Cu_LFFQ E_LFFQ Fat_LFFQ Fe_LFFQ Fibre_LFFQ
Fola_LFFQ Fru_LFFQ Glu_LFFQ I_LFFQ K_LFFQ Lact_FFQ MUFA_LFFQ Mg_LFFQ Mn_LFFQ N_LFFQ NiacE_LFFQ
Niac_LFFQ PUFA_LFFQ P_LFFQ Prot_LFFQ Ret_LFFQ Ribof_LFFQ SFA_LFFQ Se_LFFQ Star_LFFQ Suc_LFFQ
TVitA_LFFQ Thia_LFFQ VitB12_LFFQ VitB6_LFFQ VitC_LFFQ VitD_LFFQ VitE_LFFQ Zn_LFFQ b_C_LFFQ;

do i=1 to dim(missng);

  if missng(i)=0 then missng(i)=.;

  else missng(i)=missng(i);
end;
run;

[catch18]

I tried your suggestion here, but instead of changing the zeros in the dataset to . a new variable (i) was created and every observation in this new variable was given .

[hlnquynh]

Sorry, I have updated the answer. Please try again.

[andreas_lds · Accepted solution]

You will have to write a loop to check each variable separately. Are all numeric variable from Theresa.drake in xList? If that is the case you can do something like:

data work.drake;
  set Theresa.drake;
  array nums _numeric_;

  do i = 1 to dim(nums);
    if nums[i] = 0 then nums[i] = .;
  end;
run;

If not: replace _numeric_ with &xList.

[catch18]

Many thanks.

This has worked but additionally, it has created an unwanted (I) variable and given each observation 41 in this variable. I'm not familiar with array so not sure if this is expected?

[Tom]

---

@catch18 wrote:

Many thanks.

This has worked but additionally, it has created an unwanted (I) variable and given each observation 41 in this variable. I'm not familiar with array so not sure if this is expected?

---

You can use the DROP statement to not output the variable I used to index into the array.  Note that its values is always 41 because your array had 40 variables.

drop I;

Or you could use the older DO OVER syntax.

array nums _numeric_;
do over nums ;
  if nums = 0 then nums = .;
end;

[catch18]

Thank you.

[PaigeMiller]

---

@catch18 wrote:

Hi,

I'm trying to set the zeros in my dataset to missing, so SAS doesn't state it as my minimum value when I run proc means etc.

I have about 40 variables so I've used macros as follows:

%let xlist= Al_LFFQ Cal_LFFQ Carb_LFFQ Chol_LFFQ Cu_LFFQ E_LFFQ Fat_LFFQ Fe_LFFQ Fibre_LFFQ
Fola_LFFQ Fru_LFFQ Glu_LFFQ I_LFFQ K_LFFQ Lact_FFQ MUFA_LFFQ Mg_LFFQ Mn_LFFQ N_LFFQ NiacE_LFFQ
Niac_LFFQ PUFA_LFFQ P_LFFQ Prot_LFFQ Ret_LFFQ Ribof_LFFQ SFA_LFFQ Se_LFFQ Star_LFFQ Suc_LFFQ
TVitA_LFFQ Thia_LFFQ VitB12_LFFQ VitB6_LFFQ VitC_LFFQ VitD_LFFQ VitE_LFFQ Zn_LFFQ b_C_LFFQ;

/*coding 0 values to missing*/
data work.drake;
  set Theresa.drake;
   if &xlist=0 then &xlist=.;
  run;

I get an error code as below when I run it." Could you please help? Thanks

/*coding 0 values to missing*/

327 data work.drake;

328 set Theresa.drake;

329 if &xlist=0 then &xlist=.;

NOTE: Line generated by the macro variable "XLIST".

1 Al_LFFQ Cal_LFFQ Carb_LFFQ Chol_LFFQ Cu_LFFQ E_LFFQ Fat_LFFQ Fe_LFFQ Fibre_LFFQ Fola_LFFQ

--------

388

76

1 ! Fru_LFFQ Glu_LFFQ I_LFFQ K_LFFQ Lact_FFQ MUFA_LFFQ Mg_LFFQ Mn_LFFQ N_LFFQ NiacE_LFFQ

1 ! Niac_LFFQ PUFA_LFFQ P_LFFQ Prot_LFFQ Ret_LFFQ Ribof_LFFQ SFA_LFFQ Se_LFFQ

ERROR 388-185: Expecting an arithmetic operator.

ERROR 76-322: Syntax error, statement will b

---

Macro variables perform text substitution, the value of the macro variable is substituted in place of &xlist when SAS runs, and this substitution MUST result in legal valid working SAS code. So, the advice here would be to get the code to work for two variables without macros and without macro variables. If your code won't work without macros and without macro variables, then it will never work with macros and with macro variables.

Specifically, this is the code that is created when you run the program:

if Al_LFFQ Cal_LFFQ Carb_LFFQ Chol_LFFQ Cu_LFFQ E_LFFQ Fat_LFFQ Fe_LFFQ Fibre_LFFQ = 0 then ...

and this is not legal SAS code. I hope you see why this is not legal SAS code and will not work.

However, as mentioned, the use of ARRAYs here would be superior to macro variables which are not needed for this problem, and so the complications and difficulties of using macro variables also are not needed.

[catch18]

Thank you Paige Miller.