data have; input Col1 1-3 col2 $ 6-8; cards; 123       123  abc  123       123       456       456  def  456       789       789  ghi  789       789       ; run; proc sql; create table want as select h1.Col1,        h1.Col2,        PropCase(h2.col2) as Col3 from   have h1,        have h2 where  h1.Col1 = H2.Col1    and h2.Col2 ne ''; quit; ... View more

Medidata is offering also third option. Direct access using ODBC into replicated database which includes all Medidata Rave tables, so you can extract all needed data. We have build system based on these tables, we are extracting lot of tables once a day, full load for small tables every night and for big tables like datapoints we are loading incrementally during week and once in week we are doing full download. So we have all needed metadata in our datawarehouse, which we are using i.e. for creation of ClinicalViews like SAS datasets. So this happens automatized for every study every night. Data transfer is quite reliable even we are Finnish based company and we have to do transatlantic data transfer. I'm not sure if MediData is offering this for new customers, because when we started this project there was no Biostat gateway available. ... View more

First I added datepart and timeparts variables into source data just for making calculations easier. data have1;   set have;   attrib tm format=time8.;   attrib dtp format=date9.;   dt=input(dtc,datetime18.);   tm=timepart(dt);   dtp=datepart(dt); run; /*Then I created timegroups dataset for "sorting"  purposes */ data timegroups;   retain lower '00:00:00't;   attrib lower format=time8.;   attrib time format=time8.;   do time='00:00:00't to '23:59:59't by '00:02:30't;   output;   lower=time;   end; run; /* Calculition of min and max in timeframe and date */ proc sql ;   create table want1 as   select h.dtp,          tg.lower,          min(h.obs) as min_obs,          max(h.obs) as max_obs   from   timegroups tg,          have1 h   where  h.tm between tg.lower and tg.time /*h.tm gt tg.lower and h.tm le tg.time */   group  by h.dtp, tg.lower; quit; /' selecting last observation */ proc sql ;   create table want2 as   select w.*,          h.obs as last_obs   from   want1 w,          have1 h   where  h.tm between w.lower and w.lower+'00:02:30't and w.dtp = h.dtp   group  by w.dtp, w.lower   having h.dt= max(h.dt); quit; ... View more

data example; attrib trandate informat=ddmmyy10. format=ddmmyy10.; infile cards dlm=' '; input trandate amount 16; cards; 01/01/2015     5 04/01/2015     2 04/01/2015     7 06/01/2015     9 07/01/2015     3 07/01/2015     4 09/01/2015     6 ; run; data want; retain trandate amount dailybal accruingbal; set example; by trandate; if first.trandate then dailybal=0; dailybal+amount; accruingbal+amount; run; data want;   set want; by trandate; if first.trandate and not last.trandate then do;   dailybal=.;   accruingbal=.; end; run; ... View more

First I created a separate table for possible visits and I created SubjVisits table as your source table. Then I selected all subjects from that table and then I made a cartesian product to make table with all possible visits for all subjects. Then using proc sql except-clause I removed all existing visits. data visits ; input visit $ 1-20 ; cards ; CYCLE 1, DAY 1 CYCLE 1, DAY 2 CYCLE 1, DAY 3 CYCLE 1, DAY 8 CYCLE 1, DAY 15 CYCLE 2, DAY 1 CYCLE 2, DAY 8 CYCLE 2, DAY 15 CYCLE 3, DAY 1 CYCLE 3, DAY 8 CYCLE 3, DAY 15 CYCLE 4, DAY 1 CYCLE 4, DAY 8 CYCLE 4, DAY 15 CYCLE 5, DAY 1 CYCLE 5, DAY 8 CYCLE 5, DAY 15 CYCLE 6, DAY 1 CYCLE 6, DAY 8 CYCLE 6, DAY 15 ; run; /* simlulated "existing" data */ data subjvisits; input Subj $ 1-4 visit $ 6-25 ; cards; 0001 CYCLE 1, DAY 1 0001 CYCLE 1, DAY 2 0001 CYCLE 1, DAY 3 0001 CYCLE 1, DAY 8 0001 CYCLE 1, DAY 15 0001 CYCLE 2, DAY 1 0001 CYCLE 2, DAY 8 0001 CYCLE 2, DAY 15 0001 CYCLE 3, DAY 1 0001 CYCLE 3, DAY 8 0001 CYCLE 3, DAY 15 0001 CYCLE 4, DAY 1 0001 CYCLE 4, DAY 8 0001 CYCLE 4, DAY 15 0001 CYCLE 5, DAY 1 0001 CYCLE 5, DAY 8 0001 CYCLE 5, DAY 15 0001 CYCLE 6, DAY 1 0001 CYCLE 6, DAY 8 0001 CYCLE 6, DAY 15 0002 CYCLE 1, DAY 1 0002 CYCLE 1, DAY 2 0002 CYCLE 1, DAY 3 0002 CYCLE 1, DAY 8 0002 CYCLE 1, DAY 15 0002 CYCLE 2, DAY 1 0002 CYCLE 2, DAY 8 0002 CYCLE 2, DAY 15 0002 CYCLE 4, DAY 1 0002 CYCLE 4, DAY 8 0002 CYCLE 4, DAY 15 0002 CYCLE 5, DAY 1 0002 CYCLE 5, DAY 8 0002 CYCLE 5, DAY 15 0002 CYCLE 6, DAY 1 0002 CYCLE 6, DAY 8 0002 CYCLE 6, DAY 15 0003 CYCLE 1, DAY 1 0003 CYCLE 1, DAY 2 0003 CYCLE 1, DAY 3 0003 CYCLE 1, DAY 8 0003 CYCLE 1, DAY 15 0003 CYCLE 2, DAY 1 0003 CYCLE 2, DAY 8 0003 CYCLE 2, DAY 15 0003 CYCLE 3, DAY 1 0003 CYCLE 3, DAY 8 0003 CYCLE 3, DAY 15 0003 CYCLE 5, DAY 1 0003 CYCLE 5, DAY 8 0003 CYCLE 5, DAY 15 0003 CYCLE 6, DAY 1 0003 CYCLE 6, DAY 8 0003 CYCLE 6, DAY 15 ; run; proc sql; create table subjects as select distinct        subj from subjvisits; quit; proc sql;   create table missing as   select subj,          visit   from   subjects,          visits   except   select subj,          visit   from   subjVisits; quit; ... View more

Just read both into SAS and use proc compare. ... View more

Sorry that program calculated 12 forthcoming months: This should calculate 12 previous: proc sql;   create table want as   select h1.ticker,          h1.date,          std(h2.return) as return_Std,          mean(h2.return) as return_mean,          min(h2.return)  as return_min,          max(h2.return)  as return_max,          n(h2.return)    as return_n   from   have h1 ,          have h2   where  h1.ticker = h2.ticker      and h1.date ge h2.date      and h1.date le h2.date+365.25 group by h1.ticker, h1.date; quit; ... View more

I added also other calculated statistics just to check that program is working as supposed: proc sql;   create table want as   select h1.ticker,          h1.date,          std(h2.return) as return_Std,          mean(h2.return) as return_mean,          min(h2.return)  as return_min,          max(h2.return)  as return_max,          n(h2.return)    as return_n   from   have h1 ,          have h2   where  h1.ticker = h2.ticker      and h2.date ge h1.date      and h1.date ge h2.date-365 group by h1.ticker, h1.date; quit; ... View more