If the data are already sorted by ID/START, then you could:
data want (drop=i nxt_:);
merge have
have (firstobs=2 keep=id start rename=(id=nxt_id start=nxt_start));
if end^=nxt_start or id^=nxt_id then do i=1 to coalesce(dif(_n_),_n_);
set have (drop=end);
if i=1 then output;
end;
run;
The MERGE statement read a number of observations until the current END doesn't match the next START.
The do loop rereads all those observations (except the variable END). It outputs only the first of them (to get the initial START as well as initial a b c d). But it keeps the END value from the last connected obs determined via the MERGE statement.
... View more
Hi @ttqkroe,
With a suitable ORDER BY clause in a PROC SQL step you can create a huge variety of sort orders.
Example:
/* Create sample data for demonstration */
data have;
someID='001';
array _[*] othervar v_20 abc_5 k_108 abc_20 v_108 v_5 k_5 abc_108 k_20 (0:9);
run;
/* Create list of variable names, ordered like this:
1. names not containing an underscore, sorted by position in PDV
2. names containing an underscore, sorted by numeric suffix, then by prefix
*/
proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where libname='WORK' & memname='HAVE'
order by find(name,'_')>0, varnum*(~find(name,'_')), input(scan(name,2,'_'),32.), name;
quit;
%put &varlist; /* for a visual check in the log */
%put %length(&varlist); /* should be <65533 */
/* Create dataset with sorted variables */
data want;
retain &varlist;
set have;
run;
... View more
You can use next tested code:
data have;
input ID (a b c d e f g h)(:$3.);
datalines;
1 no no no no no yes no no
1 no yes no no no no no no
1 yes no no yes no no no no
2 no no yes no no no yes no
2 yes no no no no yes no no
;
run;
proc sql;
select max(a) as a,
max(b) as b,
max(c) as c,
max(d) as d,
max(e) as e,
max(f) as f,
max(g) as g,
max(h) as h
from have
group by ID;
quit;
... View more
data one;
infile datalines dlm = ',' missover dsd;
input ID a $ b c d;
datalines;
1, ,1,1,1
1, ,2,2,2
1,b,3,3,3
1, ,4,4,4
;
data two;
input ID a $;
datalines;
1 c
;
data want;
set one;
z = 1;
if a = "" then set two point = z;
run;
... View more
data have;
input ID a b c d e f;
cards;
1 1 2 3 4 5 6
1 2 . 4 . 6 .
;
data want;
update have(obs=0) have;
by id;
output;
run;
ID
a
b
c
d
e
f
1
1
2
3
4
5
6
1
2
2
4
4
6
6
... View more
