How to Macro Loop In an IF/THEN statement

[DrBigAl]

I have an issue trying to loop through an if/then statement with a macro variable list. The code is taking this year's data of a client and comparing last years data with the same client. The variable list has 80 variable names, but the if/then code will say the same as it loops through the list to output the summary...... An example of the &var_list is (doc_num, purchase_price, etc.).

proc sql;
 select distinct inputs into :var_list separated by ' '
 from work.macrolist;
quit;

data want;
 set data ;
 if &docyear.=&curryr. and missing(&var_list) and &docyear.=&prevyr. and missing(&var_list) then summary= 'Both Missing';
  else if &docyear.=&curryr. and not missing (&var_list) and &docyear.=&prevyr. and missing(&var_list) then summary= 'Previous Year Missing';
  else if &docyear.=&curryr. and missing(&var_list) and &docyear.=&prevyr. and not missing(&var_list) then summary= 'Current Year Missing';
  else if &docyear.=&curryr. and &var_list NE &docyear.=&prevyr. and &var_list then summary='Missmatch';
  else if &docyear. in (&curryr.,&prevyr.) and &var_list= &var_list then summary='Matching';
  run;
  

[ChrisNZ]

1. An example of the &var_list is (doc_num, purchase_price, etc.).

Not it's not. You chose to have a blank seperator  separated by ' '

2. The missing function takes one argument. No more no less.

Are you trying to test if all the variables are missing?

3.  if  ... &var_list=&var_list

will of course always be true (if the values are suitable)

You need to provide a lot more explanations, and cut down the data for us to understand..

Please simplify your question and report here again.

[ballardw]

Did you have code that worked with two variables in the role of &varlist that worked? That is the first step in writing a macro:

Have working code similar to what is needed. Note: two variables as in many cases things that work for one variable, such as Missing(varname) will not work with two variables: missing(var1 var2) will generate an error.

Perhaps you might use the CMISS function with a list to get an explicit number of missing values for the list. This would use a comma delimited list.

data example;
   x=3;
   y='ABC';
   z='';
   q=.;
   nummiss= cmiss(x,y,z,q);
   put nummiss=;
run;

Assign the number missing to a variable before that IF/then block. Then test the appropriate value of the variable instead of many repeated calls to the missing function.

I have to say that having 80 (!) variables and then assigning a single variable named summary as "Both Missing" implies that only two variables are considered. If you are checking 40 pairs then you need either 1) 40 summary variables or 2) 40 records, one for each pair AND a way to know which pair was considered.

Again, provide some example data, best in the form of a data step so we can test code, and the desired result for that input set.

You should have enough variables and records to exercise all of the cases. Instead of 80 variables that you use in "varlist" perhaps 3 or 4, just enough to show the input and output clearly. A solution for 3 or 4 variables usually scales up fairly easily.

[DrBigAl]

Thank you for your answer. Perhaps I didn't state my problem effectively. I am trying to compare two observations by group. For example...

Group A - Customer 123 and Group B - Customer 543:

obs       cust_num   product   date_of_purchase     Discount

  1            123            tote          ,                              Yes

  2            123            scarf        2018-03-22             Yes

  3            543            purse       2017-12-21             No

  3            543            purse       2018-03-07             Yes

How would I compare the observations with the same customer (group)? If cust 123 first date is missing, and last date present then  summary  = "First Date Missing"

or

If Cust 543 first product is the same as last product, then summary = "Matching"

For each variable (80 variables) I will need to compare first and last date and put as summary - Matching, Both Missing, First Missing, Last Missing, and etc...

Hope this clarifies what I am trying to accomplish.

[Tom]

Sounds like you want to use a BY statement on CUST_NUM to process each CUST_NUM set of observations separately.

How are the observations within a CUST_NUM group ordered? Is it by the DATE_OF_PURCHASE variable? Some other variable or just willy-nilly?

What happens if there are more than two observations for a given value of CUST_NUM?

You need to provide example output for your example input.

What type of output do you want?  If it is a dataset then supply the dataset (preferable as a SAS data step).  If it is a report then supply the example report preferable as raw text.

[DrBigAl]

I am only needing the latest of the two records for each customer. An output dataset would be desirable. So far, I have sorted the table and used first. last. to choose the two most recent events per customer. Beyond this, I am lost in how to compare within groups.

proc sort data=table out=sort1

by cust_num descending date_of_purchase;

run;

data want;

set sort1;

count + 1;
    by cust_num descending date_of_purchase;
    if first.cust_num then count=1;
    if count<=2 then output;

run;

/*compare within group of 80 varibles*/