Macro and Do loop?

[sonic_poem]

Hello,

I have a data set below. I need to set the first (or the least) count value as a macro variable (constant) where the cumulative percent (cum_pct) is at least 80.0. So, for this data set, I need to write a program obtain count of 7 (since cum_pct is >= 80.0 for the first time in this datase) and create a macro variable for it. I attempted using Do While loop and received the error messages below. Please advise.

Ct cum_pct
1 40.0
2 51.8
3 58.1
4 62.4
5 76.2
6 79.4
7 80.9
8 83.8
9 94.0
10 100.0

proc sort data=test_freq;by ct;run;
data _null_;
	do while(cum_pct>=80); 
		set test_freq ;
		by ct;
			do i=1 to _n_;
			call symput ('ctpct', ct);
			if first.ct;
		end;
	end;
run;
%let ctpct=&ctpct;
%let ctpctn=%eval(&ctpct);

ctpct resolves to character string "ct".  ctpctn resolves to _null_.

[PaigeMiller]

I don't know why you need a macro variable that contains the value 7, when a data step variable would do just as well, and its simpler. Why complicate the issue by adding in macros? Without a good reason, you are just making things harder.

data save;
    set test_freq;
    if cum_pct>=80 then do;
        count=ct;
        output;
        stop;
    end;
run;

[sonic_poem]

Thank you for the reply.  But, I need to use this value for my next step so I do need this as a macro variable.

[PaigeMiller]

---

@sonic_poem wrote:

Thank you for the reply.  But, I need to use this value for my next step so I do need this as a macro variable.

---

Since you don't specify what the next step is, the are cases where it certainly is possible to make use of it as a data step variable rather than a macro variable.

[Cynthia_sas · Accepted solution]

Hi:

  I am not sure I understand what you want exactly, however, if all you are trying to do is grab 7 as a value into a macro variable, that is easy to do without a DO loop.

The macro variable &WHATCT holds the value 7 and the macro variable &WHATVAL holds the cum_pct of 80.9. I THINK this is what you're describing.

  The where clause with a subsetting if is all you need in this case. The WHERE will select the last 4 rows of the data (row 7, 8, 9 and 10, which are all cum_pct values GE 80) and then IF _N_ = 1; will only select the first row where CT=7.

Hope this helps,

Cynthia

[sonic_poem]

Thanks, it worked!

[Tom]

Note sure how those macro variables ever got any values.  Perhaps you ran some earlier code that assigned them values?

Let's look at what your program is doing.

First you are creating a data step that will not create any output.

data _null_;

Then you have a DO loop that only execute as long as the CUM_PCT variable is 80 or larger.  Since you have not yet defined CUM_PCT the compiler will create it as a numeric variable.  When the step runs its value will be missing since there is no code that has yet given it a value so your DO loop will never execute.  So the data step will stop after the first iteration having done nothing.

do while(cum_pct>=80); 
....
end;
run;

Let's also look at the code that will be skipped as it has its own problems.

First you bring in data from TEST_FREQ dataset. And tell SAS that the data is sorted by CT and that it should create FIRST.CT and LAST.CT variables (and also fail if it finds that the values of CT are not sorted properly).

set test_freq ;
by ct;

Then you have a DO loop that is designed to repeat once on the first observation, twice on the second observation, three times on the third observation, etc.  But because of the subsetting IF statement it will only repeat once when it is the the first observation for a particular value of CT.  But since your values of CT in your example data are unique that means that it would only ever execute the DO loop once not matter what value _N_ has reached. 

do i=1 to _n_;
  call symput ('ctpct', ct);
  if first.ct;
end;

Also notice that you are always writing to the same macro variable 'CTPCT'.  So in the end only the last time that the CALL SYMPUT() runs will have any effect on the rest of the program after this data step.  Any values written to CTPCT will be overwritten by the later executions of the statement.

Also notice that you are using the old CALL SYMPUT() function instead of the newer CALL SYMPUTX() function.  So your log will have messages about converting the numeric variable CT to character values to pass to the macro variable. Also since SAS will use BEST12. format for the automatic conversion your macro variable will have leading spaces.  If you use CALL SYMPUTX() the number will be converted to character without any notes to the log and leading and trailing spaces will not be included.

Finally you have the last two macro statements. The first will remove the extra spaces caused by using CALL SYMPUT() instead of CALL SYMPUTX().  The second should do nothing since the value of macro variable should already look like a number. There is nothing %EVAL() to do.  It will convert the string into a number and convert it back into a string to store back into the macro variable.

If the goal is to find the value of CT and CUM_PCT for the first observation that has CUM_PCT >= 80 then you just need this code

data _null_;
  set test_freq ;
  where cum_pct >= 80;
  call symputx('ct',ct);
  call symputx('cum_pct',cum_pct);
  stop;
run;

%put When CT=&CT is the first place where CUM_PCT is greater than or equal to 80.;
%put When CUM_PCT=&cum_pct is the first place where CUM_PCT is greater than or equal to 80.;

[sonic_poem]

Thanks for the detailed analysis.  I'm learning to code in macro. I appreciate it.  This solution worked, too!

[Cynthia_sas]

Hi:

  The key to learning Macro programming is to have a good understanding of SAS programming first. You always have to start with a working SAS program, when you are first starting out with a Macro program -- because adding Macro language syntax to a program that doesn't work or trying to use Macro language to do something that is better done with a DATA step program -- that is not a good practice. Mixing DATA step constructs with Macro constructs generally leads to undesirable results.

  Here are some papers to help you get started understanding the difference and outlining some best practices for learning about SAS Macro Facility:

https://support.sas.com/resources/papers/proceedings/proceedings/sugi28/056-28.pdf and

https://support.sas.com/resources/papers/proceedings13/120-2013.pdf

Hope this helps,

Cynthia