Hi Laurie, I'm sorry for the late reply, with holiday travel etc I got a bit behind on things. Hope you had a great New Year! I'm SO SORRY. Thank you so much for correcting me. Based on your code, I formulated the code below, which works for me. However, there are some small problems: /* program for creating enrollment table*/
%let date=20170102;
%let datebegin = %sysevalf('26dec2016'd);
%let dateend = %sysevalf('02jan2017'd);
/*import csv*/
proc import out=work.enrollmentbysubject datafile="Z:\ADAPTABLE\Mytrus\Pickups from Mytrus\&date.\enrollmentbysubject.csv"
dbms=csv replace;
getnames=yes;
datarow=2;
guessingrows=20000;
run;
data work.enrollmentbysubject;
set work.enrollmentbysubject;
if compress(cats(CDRN), '.')=' ' then
delete;
run;
/*convert datetimes in column "Randomized" to SAS datetimes*/
data work.enrollmentbysubject;
set work.enrollmentbysubject;
Randomized_date = datepart(input(Randomized, anydtdtm23.));
format Randomized_date yymmdds10.;
run;
/*create table that I want*/
proc sql noprint feedback;
create table work.dailyenrollmenttable_&date. AS
select distinct cdrn, site,
count(Invitation_Code_Used) AS Golden_Tickets_Entered,
count(Randomized) AS Enrolled,
(calculated enrolled / calculated golden_tickets_entered) as percent_enrolled format=percent8.2
from work.enrollmentbysubject
Group by site;
quit;
proc sql noprint feedback;
create table work.patientsenrolledperweek_&date. AS
select distinct cdrn, site, count(Randomized_date) AS Patients_Enrolled_Per_week
from work.enrollmentbysubject
where Randomized_date between &datebegin and &dateend
group by site;
quit; 1) if I move the below statements into my first proc sql statement, then the table that is returned just has the patients enrolled per week. Meaning, for example Penn St has 88 Golden Tickets Entered, 28 enrolled, and 31.82% enrolled. But if I add the Patients_Enrolled_Per Week column it will put that value in all of the columns. Do you know why this is? So the way I got around that was just by making 2 separate tables and than manually inputting the "patients enrolled per week" into the other table. Doesn't seem like the most efficient thing in the world... count(Randomized_date) AS Patients_Enrolled_Per_week where Randomized_date between &datebegin and &dateend 2) Lets say for discussion purposes, that I have 15 sites that I'm gathering enrollment data for (meaning 15 rows in my table). If one site, say Montefiore Medical Center, does not enroll any patients that week, how do I populate the Patients_Enrolled_Per_Week table with a "0". Right now it only is showing me sites that do have a positive count. 3) In your code below, I don't really understand the point of your if ranuni(225465114) < .5 then
invitation_code_used = 'Y'; in your data step. Thanks again for helping me out with this, you've been a lifesaver! Pete
... View more