Sorry, not much of that post makes sense.  Please examine the guidance on presenting a question (and not that is question, not please do this work for me).  Post example data in the form of datasteps, and show what you want the output. Why is visit 5 not in valid dataset for example?  Visit 2 for 103 does not have an SD data item, yet does in the output? ... View more

@SrikanthY 1. Post your input data via a valid SAS data step creating a SAS table 2. Please don't expect people to do all the work for you. What have you done so far? Eventually post your not yet fully working code and some explanation of your approach. ... View more

thanks, it's worked. ... View more

Have a step after that to do the final part: data ds1; a=1; b=2; c=3; run; data ds2; a=2; b=3; d=1; run; data ds3; a=3; b=4; e=5; run; data _null_; set sashelp.vcolumn (where=(libname="WORK" and upcase(name) in ("C","D","E"))) end=last; if _n_=1 then call execute('data want; set '); call execute(cat(memname,' (keep=a b ',name,' where=(not missing(',name,'))) ')); if last then call execute(';run;'); run; data want (keep=a b col1 from); set want; array v{*} _numeric_; do i=3 to dim(v); if v{i} ne . then do; col1=v{i}; from=vname(v{i}); end; end; run; ... View more

I think that the following does what you want:   data want (drop=counter nmiss); do until (last.group); set have; by PatID Visit Group notsorted; if first.Group then do; counter=1; nmiss=0; end; else counter+1; if missing(value) then nmiss+1; end; do until (last.group); set have; by PatID Visit Group notsorted; if nmiss gt 0 and not missing(group)then do; if counter eq nmiss and counter gt 1 then comment='Y'; else comment='N'; end; output; end; run; Art, CEO, AnalystFinder.com   ... View more

@mkeintz wrote: I think this is what you are describing:   Form a date range using the min STDT and max EDT from a series of consecutive records.  Every record in the group must have at least a single day intersection with at least one other record in the group.  Records that are merely adjacent to a range are not included.   Is that your intention?       If my assumptions above are correct, then this program will provide what you need:   See revision comments below - (this program is incomplete).   data want; do N=1 by 1 until (last.subject or min(period_edt,nxt_stdt)); set have; by subject; merge have have (firstobs=2 keep=stdt rename=(stdt=nxt_stdt)); period_edt=max(period_edt,edt); if first.subject then period_edt=edt; end; if N>1 then OPx='output'; do I=1 to N; set have; by subject stdt edt notsorted; if first.edt=0 or last.edt=0 then comment='Duplicates' else comment='N'; output; end; run;   Notes: The program has two DO loops.  The first loops reads in a set of records (for a single "period"), and the 2nd re-reads the same set of records. The first DO N=1 by 1 until ... does the following If the record in hand overlaps with the prior cumulative period, then update the period_edt. The loop stops at the end of a subject, or if the current period_edt falls short of the stdt of the next record Then if N is >1 set "output" on The second DO rereads the same records. It has a "by subject stdt edt notsorted" to help detect when there are duplicate records   REvision:   Besides a typo in the above program, I didn't program for a subsequent record overlapping the lower end of the current period.  Also a minor fix to account for missing edt:   data want; do N=1 by 1 until (last.subject or period_edt<nxt_stdt or period_stdt>coalesce(nxt_edt,nxt_stdt) ); set have; by subject; merge have have (firstobs=2 keep=stdt edt rename=(stdt=nxt_stdt edt=nxt_edt)); period_edt=max(period_edt,edt,stdt); period_stdt=min(period_stdt,stdt); if first.subject then do; period_edt=edt; period_stdt=stdt; end; end; if N>1 then OPx='output'; do I=1 to N; set have; by subject stdt edt notsorted; if first.edt=0 or last.edt=0 then comment='Duplicates'; else comment=' '; output; end; run; ... View more

Based on your diagram, this looks like it might do the trick:   data want; Rvalue=0; do until (last.id);    set have;    by id;    Rvalue + (edate - stdate); end; do until (last.id);    set have;    by id;    subvalue = edate - stdate;    output; end; run;   ... View more

This is why it is a good idea to follow the guidance for posting questions which you will see when you post.  Provide clear test data (in the form of a datasteps) which show you problem, and full information about what needs to happen plus test output.     Why is there are requirement for SQL?   Your main problem here is that your data is transposed - this means the data goes across the table.  SQL is built specifically to work with normalised data, i.e. data going down the table.  So, step one will be getting the data into a way that SQL can work with, if you have 3 dates then you could do:   select SUBJECT_ID,DATE1 as DATE from HAVE union all select SUBJECT_ID,DATE2 as DATE from HAVE union all select SUBJECT_ID,DATE3 as DATE from HAVE From this, you can put it in a sub-query, then do max on that data:     proc sql; create table WANT as select distinct SUBJECT_ID, DATE from ( select SUBJECT_ID,DATE1 as DATE from HAVE union all select SUBJECT_ID,DATE2 as DATE from HAVE union all select SUBJECT_ID,DATE3 as DATE from HAVE ) group by SUBJECT_ID; quit;     ... View more

data x; infile cards missover; input id $ visit val flag $; cards; A1001 1 20 Y A1001 2 20 Y A1001 3 20 A1001 4 . A1001 5 20 A1002 1 20 A1002 2 20 Y A1002 3 . A1002 4 20 ; run; data want; do i=1 by 1 until(last.id); set x; by id; if flag='Y' then _i=i; end; n=0; do j=1 by 1 until(last.id); set x; by id; if j gt _i and not missing(val) then n+1; if n=1 and not missing(val) then output; end; drop i j n; run; ... View more

Good point. If the IDs are relevant you need to check them as well. DATA event; length lag1_event $2 lag1_value lag1_id 8; set event; lag1_event=lag1(event); lag1_value=lag1(value); lag1_id=lag1(id); if id eq lag1_id and event eq 'V3' and lag1_event eq 'V2' then val=value-lag1_value; drop lag1:; RUN; ... View more

Oh, not label, variable name.  In which case why?  It matters not to programming if they are upper/lower/mixed case.  If you have an output spec to match to then I would suggest creating an emtpy table which matches your spec, then setting your data to that spec.  This has the benefits of having a fixed structure, and you can pull out any problems: proc sql; create table MYOUT ( VAR1 char(200), VAR2 num ); quit; data myout; set myout processed data; run; ... View more