This is a simple question with probably a very simple fix.  I am just not sure how to do it. I want to capture some data using PROC SQL and then assign a variable with all the same value.  The example below is trying to assign an identifying variable DRUG that will let me know which drug a patient received when I stack all the data together.  Any suggestions?  Currently, drug as 'XXXXX' is not doing the trick. /***  first dose dates  ***/ proc sql;       create table ec225dates as       select unique patno, drug as 'EC0225', min(admindt) as firstdosedt format date9.       from ec225.ec225adm       group by patno;       create table ec489dates as       select unique patno, drug as 'EC0489', min(admindt) as firstdosedt format date9.       from ec489.ec0489       group by patno;       create table ec145dates as       select unique patno, drug as 'EC145', min(admindt) as firstdosedt format date9.       from fv01.ec145adm       group by patno; quit; data dosedates;       set ec225dates ec489dates ec145dates; run; ... View more

My problem has two questions: 1.  Can a macro be called in a data step? 2.  If so, how can I loop through in a single data step that will add a new line to the data table and use a value in the table as an input parameter to that macro? Here is a general example of what I want to do: ITERATION = the number of times I have gone through the loop COMPARE = the variable used to compare with RESULT MINVAL = the minimum possible value of VALUE; set at 0 on first iteration.  On all other iterations, if RESULT[i-1] < COMPARE[i-1], then MINVAL = MINVAL[i-1]; else MINVAL = VALUE[i-1] MAXVAL = the maximum possible value of VALUE; set at 1 on first iteration.  On all other iterations, if RESULT[i-1] > COMPARE[i-1], then MAXVAL = MAXVAL[i-1]; else MAXVAL = VALUE[i-1] VALUE = the midpoint between MINVAL and MAXVAL (derived as:  MINVAL + (MAXVAL-MINVAL) / 2) RESULT = The returned value from a macro call using VALUE as an input parameter ITERATION COMPARE MINVAL MAXVAL VALUE RESULT 1 0.90000 0 1 0.5 0.00088 2 0.90000 0 0.5 0.25 0.46054 3 0.90000 0 0.25 0.125 0.93546 4 0.90000 0.125 0.25 0.1875 0.75793 5 0.90000 0.125 0.1875 0.15625 0.86567 6 0.90000 0.125 0.15625 0.140625 0.90512 7 0.90000 0.140625 0.15625 0.1484375 0.88658 8 0.90000 0.140625 0.1484375 0.14453125 0.89614 9 0.90000 0.140625 0.14453125 0.142578125 0.90070 10 0.90000 0.142578125 0.14453125 0.143554688 0.89844 11 0.90000 0.142578125 0.143554688 0.143066406 0.89958 12 0.90000 0.142578125 0.143066406 0.142822266 0.90014 13 0.90000 0.142822266 0.143066406 0.142944336 0.89986 14 0.90000 0.142822266 0.142944336 0.142883301 0.90000 In the end, I just need the last value stored in VALUE.  I don't need this data table, although it would be nice to retain to make sure that it is doing what I want it to do. Here is my code so far.  It will give me the first line, but I am at a loss as to where to go from here.  I am not sure how to implement the DO loop with what I am trying to do. /***  Find the power  ***/ %macro kappa_power(rate1=,rate2=,kappa=,kappa0=,n=,alpha=0.05,sided=2);       data kappa;             p1_=&rate1.;             p_1=&rate2.;             p2_=1-p1_;             p_2=1-p_1;             pe=p1_*p_1+p2_*p_2;             p0=&kappa.*(1-pe)+pe;             p22=(p0-p1_+p_2)/2;             p11=p0-p22;             p12=p1_-p11;             p21=p_1-p11;             A=(p11*((1-pe)-(p1_+p_1)*(1-p0))**2)+(p22*((1-pe)-(p2_+p_2)*(1-p0))**2);             B=(1-p0)**2*(p12*(p_1+p2_)**2+p21*(p_2+p1_)**2);             C=(p0*pe-2*pe+p0)**2;             Q=(1-pe)**-4*(A+B-C);             output;             call symputx("Q1",Q);       run;       data kappa0;             set kappa (keep=p1_ p_1 p2_ p_2 pe);             p0=&kappa0.*(1-pe)+pe;             p22=(p0-p1_+p_2)/2;             p11=p0-p22;             p12=p1_-p11;             p21=p_1-p11;             A=(p11*((1-pe)-(p1_+p_1)*(1-p0))**2)+(p22*((1-pe)-(p2_+p_2)*(1-p0))**2);             B=(1-p0)**2*(p12*(p_1+p2_)**2+p21*(p_2+p1_)**2);             C=(p0*pe-2*pe+p0)**2;             Q=(1-pe)**-4*(A+B-C);             output;             call symputx("Q0",Q);       run;       %global power;       data _null_;             Zb=(((&kappa.-&kappa0.)*sqrt(&n.)) - (quantile('NORMAL',1-(&alpha./&sided.))*sqrt(&Q0.))) / sqrt(&Q1.);             power=probnorm(Zb);             call symputx("power",power);       run;       %put Power = &power.; %mend kappa_power; data iteration;       i=1;       phat=.90000;       minkap=0;       maxkap=1;       kappa0=minkap+(maxkap-minkap)/2;       output; run; proc sql;       select kappa0 into :kappa0 from iteration having i=max(i); quit; %kappa_power(rate1=0.8,rate2=0.8,kappa=0.375,kappa0=&kappa0.,n=200,alpha=0.05,sided=1); data power;       i=1;       power=&power.; run; data converge;       merge iteration power;       by i; run; ... View more

Is there a way to define a format that keeps a variable as numeric type but displays a character string much like the dollarw.d or percentw.d format? For example, if the value stored is "1234.56" but is linked to format dollar7.2, then the value will appear as "$1,234.56" (technically a character string due to '$' and ',' but still numeric).  Or if the value stored is "0.1234" and is linked to format percent9.1, then the value will appear as "12.3%" (technically a character string due to '%' but still numeric). Now, if I have a numeric valued stored as "1.23" with a user-defined format, can I make it appear as "1.23 mg/m2" and still retain the numeric properties?  I used to create a character variable by using:  charvar=compress(numvar) || ' mg/m2'; but I  want to retain the properties of a numeric variable so I can sort numerically and/or summarize the data while automatically displaying any units that should be attached. ... View more

I hope this is the most appropriate place to post this questions.  I understand that SAS provides SAS programs and macros to the FDA for their analysis of pharmaceutical data.  I was referenced to some macros written by Moh-Jee Ng, Division of Biometrics, Center for Drug Evaluation and Research at the FDA.  The macros were written on March 9, 1992.  The macros should be intended to analyze product stability data.  Does anyone know where I can get a hold of those macros for some analyses that we need to send to the FDA?  I cannot find anything through a Google search, and I was told that I might have more luck going through SAS than through the FDA. ... View more