data have; input x $ y $ z; id+1; cards; a j 0 b k 1 c l 0 d m 0 e o 1 f p 0 g q 0 h r 0 i s 1 ; proc sort data=have;by descending id;run; data have; set have(drop=id); if z=1 then do; idnew+1;output; call missing(x,y,z);end; else;idnew+1;output; run; proc sort data=have out=want(drop=idnew);by descending idnew; run; ... View more

why 111  1/7/2013  1/8/2013      this line has flag 1 and why not  111 2/3/2013  2/4/2013, they also have diff of 1. or may be you just want to check when admission date and discharge date are same, then you may try below code: data have; input MHN  ADMITDATE :mmddyy. DISCHARGEDATE :mmddyy.; format admitdate dischargedate yymmdd.; if ADMITDATE= DISCHARGEDATE then flag=1; datalines; 111           1/5/2013                   1/10/2013 111           1/7/2013                   1/8/2013                            111           2/3/2013                   2/4/2013 222           2/10/2013                 2/15/2013     222           2/10/2013                 2/10/2013                          222           2/14/2013                 2/14/2013                          444           5/10/2013                 5/11/2013 ; RUN; ... View more

data test(drop=obs); infile cards dlm = ','; input obs :$1000. @@; id=scan(obs,1,' '); name=scan(obs,2,' '); put id= name=; cards; A WE123-45,A GYHJK-01,A LLLLL-13,A GGGGG-01,B AAAAA-50,B BBBBB-03 ; run; output: id=A name=WE123-45 id=A name=GYHJK-01 id=A name=LLLLL-13 id=A name=GGGGG-01 id=B name=AAAAA-50 id=B name=BBBBB-03 ... View more

Hi, Try with just -> tprfiled=lag(tprfiled);  it will give null value for first row and lag values for subsequent rows. your code is giving the same value for tprfiled which must be 0 in your source data, and lagging null value to second row. ... View more

Hi Maisha, it seems you want to collapse your observations based on ID then below code will work for you, data test; input ID in_sourcefile1    in_sourcefile2  in_sourcefile3 ; cards; 1 1 . . 2 1 . . 3 1 . . 4 1 . . 36 1 . . 52 1 . . 101 1 . . 1 . 1 . 2 . 1 . 3 . 1 . 1 . . 1 2 . . 1 52 . . 1 ; proc sort data=test;by id; data test; update  test(obs=0) test; by id; run; output: ID in_sourcefile1 in_sourcefile2 in_sourcefile3 1 1 1 1 2 1 1 1 3 1 1 4 1 36 1 52 1 1 101 1 ... View more

Hi this will give you the desired output as stated in above comment: %macro test; proc sql; create table test as select *, %do i=1 %to 6; %if &i<6 %then %do; case when table1.num_var&i not in (4,5) or table1.charvar&i in (select table2.char_var from table2) then 1 else 0 end as flag&i, %end; %if &i=6 %then %do; case when table1.num_var&i not in (4,5) or table1.charvar&i in (select table2.char_var from table2) then 1 else 0 end as flag&i %end; %end; from table1; quit; %mend; %test; approach 2: static code: proc sql ; create table test as select *, case when table1.num_var1 not in (4,5) or table1.charvar1 in (select table2.char_var from table2) then 1 else 0 end as flag1, case when table1.num_var2 not in (4,5) or table1.charvar2 in (select table2.char_var from table2) then 1 else 0 end as flag2, case when table1.num_var3 not in (4,5) or table1.charvar3 in (select table2.char_var from table2) then 1 else 0 end as flag3, case when table1.num_var4 not in (4,5) or table1.charvar4 in (select table2.char_var from table2) then 1 else 0 end as flag4, case when table1.num_var5 not in (4,5) or table1.charvar5 in (select table2.char_var from table2) then 1 else 0 end as flag5, case when table1.num_var6 not in (4,5) or table1.charvar6 in (select table2.char_var from table2) then 1 else 0 end as flag6 from table1; quit; ... View more