I don't need the step of lagdate = 0; because in my code i used options missing=0; what this does is it assigns the value 0 to the missing values. ... View more

we have lag and intck function to achieve the expected output   option missing=0; data have; input col1 $ col2 :yymmdd10.; lagdate=intck('day',lag(col2),col2); format col2 date9.; cards; abc 2016/05/23 abc 2016/08/13 abc 2016/11/29 abc 2017/02/15 ; ... View more

another way   option missing=0; data have; input PatientID $ Date :mmddyy10.; lagdate=intck('day',lag(date),date); format date date9.; cards; X 01/22/2012 X 01/30/2012 X 01/31/2012 X 02/02/2012 ; ... View more

  Please dont round just convert as below    data have; x=-0.00015367; x2=put(x,8.3); put x2=; run; ... View more

data want; datetime='24MAR2008:00:00:00'dt; date=datepart(datetime); format date date9. datetime datetime19.; run; ... View more

 Hope the below information will help you to understand the code   step1: select distinct ACC_NO, PRODUCT, ACC_PLUS_PRODUCT, category,case when category='B' then 1 else 2 end as ord from have group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT,category The above code will select the distinct records based on ACC_NO, PRODUCT, ACC_PLUS_PRODUCT, category variables with a new variable ord where it assigns 1 for category B and 2 for category S Step2: using the data from step1 code will sum teh ord variable on ACC_NO, PRODUCT, ACC_PLUS_PRODUCT. By this we get value 3 for those records which have category B and S and 1 for those records which have category B alone select distinct ACC_NO, PRODUCT, ACC_PLUS_PRODUCT, sum(ord) as sum step3: We merge by left join the above code with sum variable with original dataset so that the original dataset will get the sum variable. the merging happens by a.ACC_NO=b.ACC_NO and a.PRODUCT=b.PRODUCT and a.ACC_PLUS_PRODUCT=b.ACC_PLUS_PRODUCT. we again derive a new sumamt2 variable from amt variable by case when sum>1 then sum(amt) else amt end as sumamt2, where if the sum value is 1 then amt values will be retained and if sum value is not 1 then the amt values are summed into sumamt2 variable and this happens on group by variable a.ACC_NO, a.PRODUCT, a.ACC_PLUS_PRODUCT and the having min(sort)=sort is to get the unique records on these records.   ... View more

Thanks for the clarification , please try the below code   proc sql; create table want(drop=ord sumamt amt cnt) as select *, case when cnt=1 then sum(sumamt) else amt end as sumamt2,case when category='B' then 1 else 2 end as ord from (select *, count(*) as cnt,sum(amt) as sumamt from have group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT,CATEGORY) group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT having min(ord)=ord; quit; ... View more

  data have; infile cards dlm='|'; input ACC_NO SUB_ACCNO PRODUCT$ ACC_PLUS_PRODUCT :$20. CATEGORY $ AMT; cards; 11111 | 12345 | GOLD | 11111GOLD | B | 50 11111 | 24895 | BLACK | 11111BLACK | B | 25 11111 | 24445 | BLACK | 11111BLACK | B | 75 11111 | 27894 | SILVER | 11111SILVER | B | 140 11111 | 23456 | SILVER | 11111SILVER | S | 70 11111 | 86475 | GOLD | 11111GOLD | S | 130 22222 | 34567 | SILVER | 22222SILVER | B | 100 22222 | 12458 | GOLD | 22222GOLD | B | 50 22222 | 12557 | GOLD | 22222GOLD | S | 100 33333 | 23785 | GOLD | 33333GOLD | B | 75 33333 | 12968 | SILVER | 33333SILVER | B | 110 33333 | 12789 | SILVER | 33333SILVER | S | 110 ; proc sql; create table want as select *, sum(amt) as sumamt from have group by ACC_NO, PRODUCT, ACC_PLUS_PRODUCT having category='B'; quit; ... View more