Accessing Table records (using row column indexing) inside a macro function

[dwights]

I'm trying to access table indexes with an %IF condition. The table is an output of a T-test. I'm trying to enter the %if loop and rerun the t-test when results are significant. 

Variable	Method	Variances	t-Value	D-F	Prob-t	char_var	number-var
ID	Pooled	Equal	4.12	3450	<.0001	0.000	0.000038
ID	S	Unequal	4.12	3402	<.0001	0.000	0.000038

Prob-t is a numeric value. char_var and number-var are character and numeric(increased decimal precision) transformations of Prob-t column.

Here is the code

 %if (table[1][6]<0.05 or table[1][7] = '0.000' or table[1][7] = '.') %then %do;



/*    Code here    to proc t-test         */



%end;

The if loop is not being executed for the table above. Can someone please let me know why.  

It is being executed if I change the '<' symbol to '>' 

I'm using Enterprise Guide 7.1

[Reeza · Accepted solution]

You can't access variables in a table in that manner. You need to first store the output into a data table and then capture that into a macro variable. Depending on exactly what you're doing you may have other options but based on what you've said here that's pretty much the option.

Here's an example of how that can happen:

1. ODS SELECT NONE/ALL -> can be used to control if output is generated to ODS destinations

2. PROC SQL (INTO) to capture macro variables

3. %IF/%THEN is valid in 'open code' if you're in SAS 9.4 TS1M5 or later, if you're using an older version you'll need to have a macro.

*T-Test code;
ods select none; *suppress output;
proc ttest data=sashelp.class;
    class sex;
    var weight;
    ods output ttests=demo1 summaryPanel=demo2;
run;
ods select all;

*capture p-value;
proc sql noprint;
    select probt into :TTest_PValue from demo1 where method='Pooled';
quit;


*demo output;
%put &TTEST_PValue;
%if %sysevalf(&TTEST_PValue < 0.05) %then %do;
    %put Test is significant;
%end;
%else %do;
    %put Test is not significant;
%end;

---

@dwights wrote:

I'm trying to access table indexes with an %IF condition. The table is an output of a T-test. I'm trying to enter the %if loop and rerun the t-test when results are significant. 

Variable	Method	Variances	t-Value	D-F	Prob-t	char_var	number-var
ID	Pooled	Equal	4.12	3450	<.0001	0.000	0.000038
ID	S	Unequal	4.12	3402	<.0001	0.000	0.000038

Prob-t is a numeric value. char_var and number-var are character and numeric(increased decimal precision) transformations of Prob-t column.

Here is the code

 %if (table[1][6]<0.05 or table[1][7] = '0.000' or table[1][7] = '.') %then %do;



/*    Code here    to proc t-test         */



%end;

The if loop is not being executed for the table above. Can someone please let me know why.  

It is being executed if I change the '<' symbol to '>' 

I'm using Enterprise Guide 7.1

---

[dwights]

I did store it in a data table - output_Ttest. The following condition is being executed (notice the > symbol) 

 %if (output_Ttest[1][6]>0.05 or output_Ttest[1][7] = '0.000' or output_Ttest[1][7] = '.') %then %do;

[Reeza]

You cannot access information in a table using this type of notation:

output_ttest[1][6] unless you're using SAS IML or something else, but since this is in the programming, I assumed your'e using programming. And when comparing values within macro logic, you need to use %SYSEVALF. Another option that may make more sense to you may be using CALL EXECUTE() or DOSUBL().

I'll see if I can draft a version of that. I'm guessing you're coming from R, Python or Matlab to SAS?

[Reeza]

You can use a data _null_ step to use data step logic but not generate a data set. 

In this case you can pass new commands to the system within CALL EXECUTE or DOSUBL(). 

The logic executes the same way as a standard data step - I'm not sure how much you know of that so if need more details please let us know. 

Here's a full data step approach. 

*T-Test code;
ods select none;
*suppress output;

proc ttest data=sashelp.cars;
    where origin in ('USA', 'Asia');
    class origin;
    var mpg_city;
    ods output ttests=demo1;
run;

ods select all;

data _null_;
    set demo1 (where=(method='Pooled'));

    if probt < 0.05 then
        do;
            call execute ('title "demo proc"; proc ttest data=sashelp.class; class sex; var height;run;');
        end;
    else
        do;
            call execute("title 'This is not significant'; proc print data=demo1 noobs label; run;");
        end;
run;

[Tom]

---

@dwights wrote:

I did store it in a data table - output_Ttest. The following condition is being executed (notice the > symbol) 

 %if (output_Ttest[1][6]>0.05 or output_Ttest[1][7] = '0.000' or output_Ttest[1][7] = '.') %then %do;

---

If by table you mean dataset then why are you using %IF instead of IF?

[dwights]

Thanks for replying. Because it is inside a macro function
- Dwight

[dwights]

Thank you very much! I totally forgot about the select into macro statement.  However in my case (select num_var into :TTest_PValue)  works instead of (select  probt into :TTest_PValue) because for probt, the  TTest_PValue is going to return <.0001 which isn't very useful.  And yes, I have a Python background.

Thank you!

Now I can return to my beet farm.