If Then Else in Marco

[altadata1]

I created this macro. But, there is no "Qunt" in the data set "wage". 

I received SAS warning that: The variable Qunt in the DROP, or RENAME list has never been referenced. 

Any tips would be much appreciated. 

%macro quantile(var1=, var2=, var3=, var4=);

DATA wage; SET AVG_wage;

 

                        %IF compyr = '2007-08' and sex = '1' %THEN %DO;

                             %IF   nocR3 = &var1 %then %do;

                                   if realwages <&var2 then Qunt= 1; else if &var2 <realwages <&var3 then Qunt= 2; else if realwages >&var3 then Qunt= 3; else  Qunt= .; %end;

                                                      %else %IF md_nocR3 = &var4 %then %do;

                                  if realwages <&var2 then Qunt= 1; else if &var2 <realwages <&var3 then Qunt= 2; else if realwages >&var3 then Qunt= 3; else  Qunt= .; %end;

 


                        %END;

                        keep sex compyr nocR3 Qunt;

                  RUN;

 

%mend quantile;

            options symbolgen;

            %quantile(var1='00', var2=47.5 , var3 =95.9);

            %quantile(var4='11', var2=22.2, var3 =51.5;

            %quantile(var4='12', var2=25.1 , var3 =42.8);

            %quantile(var4='13', var2=23.2 , var3 =32.0);

[ballardw]

You do not use MACRO %if/%then with DATA set variables. Use regular if/then/else with the data set variables.

The macro processor basically does not see data set variables.

---

@altadata1 wrote:

I created this macro. But, there is no "Qunt" in the data set "wage". 

I received SAS warning that: The variable Qunt in the DROP, or RENAME list has never been referenced. 

Any tips would be much appreciated. 

%macro quantile(var1=, var2=, var3=, var4=);

DATA wage; SET AVG_wage;

 

                        %IF compyr = '2007-08' and sex = '1' %THEN %DO;

                             %IF   nocR3 = &var1 %then %do;

                                   if realwages <&var2 then Qunt= 1; else if &var2 <realwages <&var3 then Qunt= 2; else if realwages >&var3 then Qunt= 3; else  Qunt= .; %end;

                                                      %else %IF md_nocR3 = &var4 %then %do;

                                  if realwages <&var2 then Qunt= 1; else if &var2 <realwages <&var3 then Qunt= 2; else if realwages >&var3 then Qunt= 3; else  Qunt= .; %end;

 


                        %END;

                        keep sex compyr nocR3 Qunt;

                  RUN;

 

%mend quantile;

            options symbolgen;

            %quantile(var1='00', var2=47.5 , var3 =95.9);

            %quantile(var4='11', var2=22.2, var3 =51.5;

            %quantile(var4='12', var2=25.1 , var3 =42.8);

            %quantile(var4='13', var2=23.2 , var3 =32.0);

---

[altadata1]

Thank you for the respond.

The problem is that I have 33 different values for each variables of var1 to var 4.

Mandana

[PaigeMiller]

---

@altadata1 wrote:

Thank you for the respond.

The problem is that I have 33 different values for each variables of var1 to var 4.

Mandana

---

That is not the problem with your code. That is a different problem. First, as stated above, you have to create working SAS code without macros and without macro variables for (let's say) two of the different values. You have to get that to work, because if you can't get that to work without macros and without macro variables, then it will NEVER work with macros or with macro variables.

So, please show us that working SAS code without macros and without macro variables for (let's say) two of the different values. Once you show us that, we can help you write code that works for 33 different values and four variables.

[altadata1]

This is the working code: 

DATA wage; SET AVG_wage;

             IF md_compyr = '2007-08' and sex = '1' THEN DO;
                            IF    md_nocR3 = '00' then do;
                                  if realwages <47.5 then Qunt= 1; else if 47.5 <realwages <95.9 then Qunt= 2; else if realwages >95.9 then Qunt= 3; else  Qunt= .; End;

                           ELSE IF md_nocR3 = '11' then do;
                                  if realwages <22.2 then Qunt= 1; else if 22.2 <realwages <51.5 then Qunt= 2; else if realwages >51.5 then Qunt= 3; else  Qunt= .;  End;

                END;

                        keep sex md_compyr md_nocR3 Qunt ;

                  RUN;  

[Tom · Accepted solution]

Random indentation of the code is probably worse then no indentation.

data wage;
  set AVG_wage;
  if md_compyr = '2007-08' and sex = '1' then do;
    if md_nocR3 = '00' then do;
      if realwages <47.5 then Qunt= 1;
      else if 47.5 <realwages <95.9 then Qunt= 2;
      else if realwages >95.9 then Qunt= 3; 
      else Qunt= .; 
    end;
    else if md_nocR3 = '11' then do;
      if realwages <22.2 then Qunt= 1; 
      else if 22.2 <realwages <51.5 then Qunt= 2; 
      else if realwages >51.5 then Qunt= 3; 
      else Qunt= .;  
    end;
  end;
  keep sex md_compyr md_nocR3 Qunt ;
run;

So it looks like you are converting REALWAGES into QUNT based on a set of cutoff values that depend on the values of SEX MD_COMPYR and MD_NOCR3.

I would recommend setting the cutoff values into a dataset and them merging by those three variables.  Example:

data cutoffs;
  input md_compyr $ sex $ md_nocR3 $ cutoff1 cutoff2 ;
cards;
2007-08 1 00 47.5 95.9
2007-08 1 11 22.2 51.5 
;

data avg_wage;
  input md_compyr $ sex $ md_nocR3 $ realwages;
cards;
2007-08 1 00 45
2007-08 1 00 47.5
2007-08 1 00 50
2007-08 1 00 95.9
2007-08 1 00 98
2007-08 1 11 20
2007-08 1 11 22.2
2007-08 1 11 30
2007-08 1 11 51.5 
2007-08 1 11 60
;
 
data wage ;
  merge avg_wage cutoffs;
  by md_compyr sex md_nocr3 ;
  if missing(realwages) then Qunt=.;
  else if realwages < cutoff1 then Qunt=1;
  else if realwages < cutoff2 then Qunt=2;
  else Qunt=3;
run;

proc print;
run;

Note: Your original code is not assigning QUNT a value when it happens to fall exactly on either of the cut off values.  You might want to adjust one or more of the < operators to <= operators instead depending on which bin you want the values that fall exactly at the cuttoff to land in.

[altadata1]

Thank you sooooooo much. It works perfect. 

[Tom]

Your %IF conditions can never be true.  Look at the first one:

%IF compyr = '2007-08' and sex = '1' %THEN %DO;

SAS will compare strings byte by byte until there is a difference.  A lowercase letter C will never equal a single quote character so the first test is FALSE.  

The second %IF (which will never execute because the first is never going to be true)

  %IF   nocR3 = &var1 %then %do;

might possibly be true since here you are comparing the string nocR3 to the value of the macro variable VAR1.  But none of your macro calls use anything like nocR3 as the value of VAR1.  Three of them do not set any value for the VAR1 parameter.  And the fourth has another quoted string which will never match nocR3.

If you want to test the value of dataset variables named COMPYR or SEX or NOCR3 then you need use actual SAS code instead of macro code.  So you probably want regular IF statements.

[altadata1]

Thank you for the respond. 

I don't understand this "But none of your macro calls use anything like nocR3 as the value of VAR1". Could you explain it a little bit more? 

I can use regular IF The DO, but I have 33 different values for each macro variables of var 1 to var 4. it means 33 different values for var1, 33 values for var2.... 

Any tips to make it easier or less prone to error? 

BTW, I did test the regular if then do and it works. 

Many thanks again,

[Tom]

---

@altadata1 wrote:

Thank you for the respond. 

I don't understand this "But none of your macro calls use anything like nocR3 as the value of VAR1". Could you explain it a little bit more? 

I can use regular IF The DO, but I have 33 different values for each macro variables of var 1 to var 4. it means 33 different values for var1, 33 values for var2.... 

Any tips to make it easier or less prone to error? 

BTW, I did test the regular if then do and it works. 

Many thanks again,

---

What do the values of the macro parameters VAR1 to VAR4 represent? 

Are they supposed to be the NAMES of the variables you want to check with the generated SAS code?  If so you would use the macro variable like this to generate an IF statement where the NAME of the variable being tested changes:

   if &var1 = 'YES' then do;

Are they supposed to be the VALUES of variables you want to check?  If so then you can use it like this to test whether a SPECIFIC variable has that value.

    if CONDITION_CODE = &var1 then do;

In this example if CONDITION_CODE is a numeric variable then values you would pass to VAR1 would be strings that look like numbers, such as 12 or 2.5.  But if CONDITION_CODE is a character variable then the strings you pass in var1 will need to be quoted strings, such as 'YES' or "NO". 

[Kurt_Bremser]

BEFORE you engage in macro programming, you MUST have working code without any macro elements which solves a single, non-dynamic instance of your issue.

Then, you identify the parts which need to be dynamic, and replace only those with macro variables or create them through macro statements.

So please show us the initial SAS code.

[altadata1]

Thank you for your respond. Here is the non-macro syntax for the test and it works well. 

ATA wage; SET AVG_wage;

             IF md_compyr = '2007-08' and sex = '1' THEN DO;
                            IF    md_nocR3 = '00' then do;
                                  if realwages <47.5 then Qunt= 1; else if 47.5 <realwages <95.9 then Qunt= 2; else if realwages >95.9 then Qunt= 3; else  Qunt= .; End;

                           ELSE IF md_nocR3 = '11' then do;
                                  if realwages <22.2 then Qunt= 1; else if 22.2 <realwages <51.5 then Qunt= 2; else if realwages >51.5 then Qunt= 3; else  Qunt= .;  End;

                END;

                        keep sex md_compyr md_nocR3 Qunt ;

                  RUN;