This should get you started.  I couldn't match some of your results given the formulas listed.  Also, having column names with spaces is generally a bad idea. proc sql; create table work.summary as select specialty_pharmacy, product, 'Milestone Period'n, sum(metric='AR')/sum(Milestone_Type='Referral') as Approval_Rate format=percent7.2, sum(case when metric='AR' then 'Days Achieved'n else 0 end) / sum(metric='AR') as Days_To_Approval format=comma5.2, sum(metric='FR')/sum(Milestone_Type='Referral') as Fulfillment_Rate format=percent7.2, sum(metric='FR') as Days_To_Fullfilment, sum(case when metric='COMP' then days_of_therapy else 0 end) / sum(case when metric='COMP' then 'Days Achieved'n else 0 end)   as Compliance format=comma5.2, count(*) as Records from work.input group by specialty_pharmacy, product, 'Milestone Period'n ; quit; ... View more

Perhaps a more straightforward approach: proc sql; update transactions t1 set desc=(select description from inventory where item_no=t1.item_no) where exists (select * from inventory where item_no=t1.item_no); quit; You also might want to ensure the column is the correct width as the values could be truncated. ... View more

proc sql; create table work.anova_obs as select * from work.obs where value > (select min(value) from work.obs) and value <(select max(value) from work.obs); quit; That should work if the value column doesn't have duplicates.  if it does and you only want to exclude one of them, then something like this might work. proc sql; select count(*)-1, count(*)-2 into :FirstObs, :SecondObs from work.obs; quit; proc sort in=work.obs out=work.obs; by value; run; option obs=&FirstObs; data work.temp; set work.obs; run; proc sort in=work.temp out=work.temp; by descending value; run; option obs=&SecondObs; data work.anova_obs; set work.temp run; option obs=max; ... View more

I may not be understanding your desired output and I certainly don't understand your data, but if you'd like to have one row per dx, you might try something like this: data Dx_List (keep=chartno acctno sepdate diagtyp diagpre diagclust diagcde); set mylib.data_2011; array dtyp(25) diagtyp1 - diagtyp25; array dpre(25) diagpre1 - diagpre25; array dclust(25) diagclust1 - diagclust25; array dcde(25) diagcde1 - diagcde25; do j = 1 to 25;      if dcde(j) >'' then do;           diagtype=dtype(j);           diagpre = dpre(j);           diagclust=dclust(j);           diagcde = dcde(j);           output;      end; end; run; ... View more

Let's try converting them both to numbers: proc sql; create table work.final as select s1.id, s1.product, s1.type, s2.productname from xxx.store1 left outer join xxx.store2 on input(s1.id,$5.)=input(s2.id,$5.); quit; ... View more

Assuming they are both characters (otherwise you wouldn't need to do this): proc sql; create table work.final as select s1.id, s1.product, s1.type, s2.productname from xxx.store1 left outer join xxx.store2 on s1.id=put(input(s2.id,$3.),z5.); quit; ... View more

Assuming they are both characters (otherwise you wouldn't need to do this): proc sql; create table work.final as select s1.id, s1.product, s1.type, s2.productname from xxx.store1 left outer join xxx.store2 on s1.id=put(input(s2.id,3.),z5.); quit; ... View more

I think all you need to do is change the format of the date columns to yymmddn8. Do you need to add the "}" at the end of the second date? ... View more

proc sql; create table work.store3 as select t1.id, t1.product, t2.productname, t1.type from work.store1 t1 left outer join work.store2 t2 on t1.product=t2.product; quit; ... View more

I have seen this before. I think I corrected it by using: proc sql; create table sum1 as select code , month(date_) as month , year(date_) as year , sum(col_X) as sum_ from table1 group by code,calculated month, calculated year; quit; I believe it is a true statement that SAS has not implemented ANSI SQL standards. ... View more

If I remember by program data vector correctly, the PDV is filled in as the input data sets are read. The code you have included should not cause a problem. You might have an issue with missing values which might propogate to your answer. ... View more

I think you need to use the IN option on the datasets. If age is a number, then the age='X' won't work. DATA work.data3; MERGE work.data1(in=d1) work.data2(in=d2); BY policy location; IF not d1 and not d2 THEN age='X'; RUN; ... View more