You need it to be in the having rather than the where. Where is for when reading things in, having can be used once things have been calculated. select distinct (header_1) as uniquenumbers from table having (count(header_1)) = 1; quit; ... View more

I wold use datdif in that case. Provided that you want the statement to be that b.date occurred within 1 to 31 days after a.date the below code would work, if you wanted before you could either switch a.date and b.date in the statement or change the between to -31 and -1. the having statement would be having (datdif(a.date,b.date,'act/act')) between 1 and 31 ... View more

Ok so the way that I understand this is that you are going to have a dataset which has dates and a flag 1/0 if the office was open and therefore the program should run. So this is just an example of what the dates would look like if they were read in for explanation purposes. %macro opendt; data open;      input  date open ;     informat date mmddyy10. open best2.;     format date mmddyy10.; datalines; 010113    0 010213    1 010313    1 ; run; proc sql noprint; select date into open_list separated by '*' from open where open =1; quit; %let open_cntlist = &sqlobs; %let print_date = %sysfunc(today(), MMDDYY10.); %let yesterday = %sysfunc(intnx(day, %sysfunc(InputN(&print_date,MMDDYY10.)),-1),MMDDYY10.); %do I = 1 %to &open_cntlist.; %Let opendt = %scan(&open_list.,&I.,'*'); %if &opendt=&yesterday %then %do; /*Insert code to run report*/ /*Insert email code for report*/ %end; %else %do; /*insert email code for not open yesterday*/ %end; %mend; The report will be skipped and only an email saying that the office wasn't open will be sent if yesterday was not a day that was open. ... View more

One other thing although this won't get an email into your sent box, you can save the log from the submitted program which will tell you if the email was sent. This is what the log file looked like from the above email: NOTE: The file TSC is:       E-Mail Access Device Message sent       To:          "xxxx@xxx.org"       Cc:          "overmar@ccf.org"       Bcc:               Subject:     Tscores for Thursday, Jan 2, 2014       Attachments: "xxxx.pdf" NOTE: 9 records were written to the file TSC.       The minimum record length was 0.       The maximum record length was 151. NOTE: The data set WORK.NULL has 1 observations and 0 variables. NOTE: DATA statement used (Total process time):       real time           0.81 seconds       cpu time            0.04 seconds In the log file if it says "Message Sent" then you can reasonable infer that the message was in fact sent even without a copy of it in sent items. ... View more

Abraham, I send a daily and weekly email based on a report and had the same issue of it not showing up in my sent items folder of outlook. I send these reports as a scheduled task so it may differ if you were to send it in a normal SAS session rather than a batch. The thing is if you were to cc yourself on the email (which I do) you will see that the email is actually being sent from "overmar@</computer or server id/> on behalf of overmar@ccf.org", which means that although it is routed through your email, its not really routed through outlook (see below for actual outlook header). So the easiest thing likely is cc yourself on the email so at least you then have a copy of the email being sent. ... View more

For reference you also could do it using proc sql this way: proc sql; create table temp1 as %do y=&startyr %to &endyr;  %do i = 1 %to 12; %if &i <10 %then %do; select distinct * from abc.myfile&y.0&i %end; %else %do; select distinct * from abc.myfile&y.&i %if &y =&endyr and &i = 12 %then %do; ; %end; %else %do; union corr %end; %end; %end; %end; quit; ... View more

Format(Sum(Val(IIf([dataset1].[Var1]=0,0,IIf([dataset1].[var2]=0,1,IIf([dataset1].[var1] Is Null Or [dataset1].[var2] Is Null,0,IIf(Abs([dataset1].[var1]/[dataset1].[var2])>=1,1,     IIf(Abs([dataset1].[var1]/[dataset1].[var2])<1,Abs([dataset1].[var1]/[dataset1].[var2])))))))),"Fixed") AS SCORE For reference it doesn't look like there is a false statement in the iif statements so I am expecting that false in this case would report as missing, but I could be wrong. Converts to proc sql; create table ??? as select ID, (sum(Case when dataset1.var1 = 0 then 0 when dataset1.var2 = 0 then 1 else . end), (case when dataset1.var1=. then 0 when dataset1.var2 = . then 0 else . end), (case when (abs(dataset1.var1/dataset1.var2)) >=1 then 1  else (abs(dataset1.var1/dataset1.var2)) end)) format=(whatever the "Fixed" format is) as score from dataset1 group by ID; quit; The question though that I would need answered before figuring anything else out would be should var1 and var2 be evaluated separately, ie should you be asking if var1 = 1 then 1 else if var1 = 0 then 0 else if var1 = . then 0 or do you want to include both in each of the statements? The easiest way to rewrite this is to say what each of the conditions are (as I understand them) if dataset1.var1 = 0 then 0 if dataset1.var2 = 0 then 1 if dataset1.var1 = . then 0 if dataset1.var2 = . then 0 if abs(dataset1.var1/dataset1.var2) >= 1 then 1 if abs(dataset1.var1/dataset1.var2) < 1 then abs(dataset1.var1/dataset1.var2) write a case statement for each of these (similar to what I wrote earlier including the else (value) end statements), put a sum statement outside of all of the case statements, and group by the unique ID. This should give you the same report values as long as you specify the variables in the same way. ... View more

Ok so first off you want to take only those cases who meet your first criteria which is the first visit was after April 1, 2012. For reference the dataset you put out doesn't have anyone who meets that criteria, but you can run this code to take out all of the other people (I specify in the below code that the date is April 1, 2012. proc sql; create table um2 as select distinct PID, date, docid, specialty, dx, fyear, (max(date)) format=mmddyy10. as last_dt /*You specify what the most recent date was so you can compare to it in the next step*/ from dat group by PID having (min(date)>'01Apr2012'd); /*Here you specify that the first visit for the PID has to have occurred after this date (April 1, 2012) and remove all PID who do not meet this criteria*/ create table um3 as select distinct PID, docid, dx, specialty /*Since you now know what the most recent visit date is, you can specify that the visit that you are counting had to have occurred within a specific time frame, in this case 1 year or 365 days. I wrote it that the most recent date is first so you have to specify datdif to be negative, but you could reverse it. Also I included 0, so the last visit is counted*/ from um2 where datdif(last_dt, date, 'act/act') between -365 and 0; create table um4 as /*since you have eliminated all of the visits which didn't occur within 1 year you now can reduce your visits based on dx and specialty, and count the number of DocID's that are in the table for each PID*/ select distinct PID, dx, specialty, (count(docid)) as count from um3 group by pid, dx, specialty; create table cohort as select distinct pid, dx, specialty from um4 where count >=3; /*This line could have been specified in um4 as having count >=3 or it can be written as another line. This specification will give you only those PID who had at least 3 visits to different docids in the year preceding the most recent visit for the same dx and in the same specialty*/ quit; This code should create the cohort that you have so far specified. ... View more