SQL - dictionary

/* look for tables, columns using LABEL */

PROC SQL;
  SELECT DISTINCT memname, NAME, LABEL, TYPE, LENGTH
  FROM DICTIONARY.COLUMNS
  WHERE UPPER(LIBNAME) EQ 'RAW' & UPPER(LABEL) like '%OUTPATIENT%'
; QUIT;

PROC SQL;
  SELECT DISTINCT NAME, LABEL, TYPE, LENGTH
  FROM DICTIONARY.COLUMNS
  WHERE UPPER(LIBNAME) EQ 'SDTM' & UPPER(MEMNAME) EQ 'AE'
; QUIT;

Clopper-Pearson (Exact) CI

data prop;
  input y @@;
  datalines;
  0 1 0 0 0 0 1 0
  ;
 
ods select none;
proc freq data=prop;
  tables y / binomial (exact level='1') alpha=0.05;
  ods output binomialcls=ci;
run;
ods select all;

proc print data=ci; run;

Array and Proc Transpose

/* using array instead of proc transpose */

data ex;
  array xx[5] x1-x5;
  array yy[5] y1-y5;

  input id $  x1 x2 x3 x4 x5 y1 y2 y3 y4 y5;

  do time=1 to 5;
    x=xx[time];
    y=yy[time];
    output;
  end;
  keep id time x y ;
  datalines;
01 3   2  4  7  4    3  5  2  2  5
02 9   3  7  5  3    2  6  4  3  8
;


Obs    id    time    x    y

  1    01      1     3    3
  2    01      2     2    5
  3    01      3     4    2
  4    01      4     7    2
  5    01      5     4    5
  6    02      1     9    2
  7    02      2     3    6
  8    02      3     7    4
  9    02      4     5    3
 10    02      5     3    8

Independent t-test with PROC MIXED

proc ttest data=anorexia (where=(treat in ("Cont","CBT"))); 
  class treat;
  var prewt;
run;

/* equal variance */
proc mixed data=anorexia (where=(treat in ("Cont","CBT"))); 
  class treat;
  model prewt = treat;
run;

/* unequal variance */
proc mixed data=anorexia (where=(treat in ("Cont","CBT"))); 
  class treat;
  model prewt = treat / DDFM=Satterthwaite;
  repeated / group=treat;
run;

Paired t-test with PROC MIXED

proc import datafile="C:\projects\Endocrine\CGMS\data\ex\anorexia.csv" out=anorexia 
            dbms=csv replace; getnames=yes;
run;

proc sql;
  create table cbt_long as
  select  var1 as patient, 0 as time, prewt as y 
  from anorexia (where=(treat="CBT"))  
  union
  select  var1 as patient, 1 as time, postwt as y 
  from anorexia (where=(treat="CBT"))  
;quit;

proc mixed data= cbt_long;
  class patient;
  model y = time / s;
  random patient;
*  repeated / subject=patient type=cs rcorr;
run;

/* Paired t-test with SQL */
proc sql;
  select t(postwt-prewt) as t, prt(postwt-prewt) as p_value
  from anorexia (where=(treat="CBT"))  
;quit;

PROC SQL: functions

COUNT, FREQ, N: number of nonmissing values

NMISS: number of missing values

MIN: smallest value

MAX: largest value

RANGE: range of values

SUM: sum of values

SUMWGT: sum of the WEIGHT variable values(footnote 1)

AVG, MEAN: means or average of values

T: Student's t value for testing the hypothesis that the population mean is zero

PRT: probability of a greater absolute value of Student's t

USS: uncorrected sum of squares

CSS: corrected sum of squares

VAR: variance

STD: standard deviation

STDERR: standard error of the mean

CV: coefficient of variation (percent)

array & output

%let ntime=288;
data series (drop=j);
array c(30) ;
array s(30);
  do time=1 to &ntime; 
    do j=1 to 30;
      c[j]=cos(2*3.141593*j* time/&ntime); s[j]=sin(2*3.141593*j* time/&ntime);
    end;
    output;
  end;
run;