Cannot get macros to work

[nfatahi]

Hi! 

I'm trying to write a macro that will pick a base mean and compare all the other means in my dataset to that mean (by doing a t-test). I also want to compare all the means to the international mean. I want my ouput to be a table that has the country ID (IDCNTRY), the mean (mnpv), the standard error (mnpv_se), the t value, and the label for 'H' or 'L' to indicate if the means are significantly higher or lower.  Here is what I have but I keep getting errors: 

%MACRO T(YEAR=, FILE=, base=, scale_name=, TABLE=);

    %put TABLE: &TABLE.;

ODS SELECT NONE;

    proc sql;

        select mnpv, mnpv_se into :mean_base, :mean_se_base

        from OUTDIR.&FILE._&TABLE.

        where IDCNTRY="&base";

    quit;

    proc sql;

        CREATE TABLE &scale_name. AS 

        SELECT

            IDCNTRY,

            mnpv AS &scale_name._Mean,

            mnpv_se AS &scale_name._SE,

            mnpv_se * mnpv_se AS &scale_name._SE_Square,

            /* international average */

            (mnpv - 496) / mnpv_se AS &scale_name._t_500,

            ((mnpv - 496) / mnpv_se ) / 1.96 AS &scale_name._t_cv_500,

            CASE WHEN calculated &scale_name._t_cv_500 >= 1 THEN ' H'

                 WHEN -1 < calculated &scale_name._t_cv_500 < 1 THEN ' '

                 WHEN calculated &scale_name._t_cv_500 <= -1 THEN ' L'

            END AS &scale_name._sig_500,

            /* comparing to base group */

            (mnpv - &mean_base.) / sqrt(mnpv_se**2 + &mean_se_base.**2) AS &scale_name._t_base,

            ((mnpv - &mean_base.) / sqrt(mnpv_se**2 + &mean_se_base.**2)) / 1.96 AS &scale_name._t_cv_base,

            CASE WHEN calculated &scale_name._t_cv_base >= 1 THEN ' H'

                 WHEN -1 < calculated &scale_name._t_cv_base < 1 THEN ' '

                 WHEN calculated &scale_name._t_cv_base <= -1 THEN ' L'

            END AS &scale_name._sig_base

        FROM OUTDIR.&FILE._&TABLE.

        ORDER BY IDCNTRY;

    QUIT;        

    proc sql;

        CREATE TABLE &scale_name._2 AS

        SELECT *

        FROM &scale_name.

        ORDER BY IDCNTRY, &scale_name._Mean DESC;

    QUIT;

    ods select all;

    PROC PRINT DATA=&scale_name.;

        TITLE "&TABLE.: &scale_name.";

        FORMAT _NUMERIC_ 20.5;

    RUN;

%MEND T;

/*----------------------------------------------------------*/

/* Table 1.     */

%T(YEAR=2018,

FILE=D2018,

base=USA

scale_name= CIL

TABLE= TABLE1);

What am I missing?? 

[SASKiwi]

There's an obvious problem with how you are calling the macro - you are missing commas between parameters:

%T(YEAR=2018,
FILE=D2018,
base=USA,
scale_name= CIL,
TABLE= TABLE1);

[Tom]

Hiding continuation characters, like the commas in your macro call, at the END of the line makes much more difficult for a human to scan the code and confirm it meets the required pattern.

%T(YEAR=2018
 ,FILE=D2018
 ,base=USA
 ,scale_name= CIL
 ,TABLE= TABLE1
);

Turn on the MPRINT option before the macro call to see what SAS code the macro call ends up generating.

[nfatahi]

Thank you for this suggestion. It seems that there might be a problem with these expressions: 

&mean_base. 

&mean_se_base.

I don't think I'm defining these correctly. I want the mean_base to be the mean math score for USA. I have a mean math score variable (mnpv) and a country variable (IDCNTRY). 

[Tom]

So the issue is not in RUNNING the macro?

It sounds like instead the issue is in getting the macro to generate code that does what you want.

In that case you need to describe what you WANT.

Can you provide example input data and the desired output for that input?

Can you describe (in words) what you are trying to calculate?

Are you sure you need a macro?  Can you get what you want by just writing SAS code instead of writing a macro to generate SAS code?

[nfatahi]

I appreciate your patience. I'm a new SAS user and still trying to understand SAS. I don't need macros to do what I want, but I want to eliminate human error by having to copy-paste numbers. Please let me know if I should just not use macros. 

This is my dataset:

I want to do t-tests, comparing the mean(mnpv) for United States with all of the other countries. I want my output to be a table with the country name, the mean, the standard error(mnpv_se), and the result of the t-test (the calculated t value and a label that tells me if the mean is higher or lower than the USA). 

I can just copy and paste the mean for the United States but I'm doing this close to a hundred times to it will be lots of copy pasting. So I was hoping I can just tell SAS to use the USA mean as the 'base mean'. 

Thanks for all the help! 

[Tom]

So you have a STATISTICs question.

You are using SAS, not FORTRAN or SQL.  You probably can do it easily enough with a simple statistical procedure.   

You appear to have already summarized the data to the country level.  I see something that looks like a COUNT in addition to the MEAN and other statistics that are as clear.

Do you have the actual raw data also?  

[Tom]

Don't try to stuff DATA into macro variables.  Leave it in datasets.

Looks like you want to make two new numerical VARIABLES (not macro language symbols with text strings in them).

%let dsname=OUTDIR.&FILE._&TABLE.;
data base ;
  set &dsname;
  where IDCNTRY="&base";
  keep mnpv mnpv_se;
  rename mnpv=mean_base mnpv_se=mean_se_base;
run;

Which you can then remerge with your other data and make your new variables.

So something like:

data &scale_name. ;
  set &dsname (keep=IDCNTRY mnpv mnpv_se);
  if _n_=1 then set base;
  rename mnpv =  &scale_name._Mean mnpv_se = &scale_name._SE ;

  &scale_name._SE_Square = mnpv_se * mnpv_se;
  &scale_name._t_500 = (mnpv - 496) / mnpv_se ;
  &scale_name._t_cv_500 = ((mnpv - 496) / mnpv_se ) / 1.96 ;
  if &scale_name._t_cv_500 >= 1 THEN &scale_name._sig_500=' H';
  else if -1 < &scale_name._t_cv_500 < 1 THEN &scale_name._sig_500=' ';
  else if .z < scale_name._t_cv_500 <= -1 THEN &scale_name._sig_500=' L';

  &scale_name._t_base = (mnpv - mean_base) / sqrt(mnpv_se**2 + mean_se_base**2);
  &scale_name._t_cv_base = ((mnpv - mean_base) / sqrt(mnpv_se**2 + mean_se_base**2)) / 1.96 ;
  if &scale_name._t_cv_base >= 1 THEN &scale_name._sig_base=' H';
  else if -1 < &scale_name._t_cv_base < 1 THEN &scale_name._sig_base=' ';
  else if .z < &scale_name._t_cv_base <= -1 THEN &scale_name._sig_base=' L';
run;