how to update a data set with more records

[knveraraju91]

Dear,

I need to add more records to data set by id,trt,visit if there are any missing records. Please suggest.

I need to add the records in red. For each, id,trt,visit  i ineed to have four records with extptnum 1 to 4

output  needed

1 p CYCLE1 1
1 p CYCLE1 2
1 p CYCLE1 3

1 p CYCLE1 4

1 p CYCLE2 1
1 p CYCLE2 2
1 p CYCLE2 3
1 p CYCLE2 4
1 p CYCLE3 1

1 p CYCLE3 2
1 p CYCLE3 3
1 p CYCLE3 4
1 p CYCLE4 1
1 p CYCLE4 2
1 p CYCLE4 3
1 p CYCLE4 4

data have;
input id trt $ visit $ extptnum;
datalines;
1 p CYCLE1 1
1 p CYCLE1 2
1 p CYCLE1 3
1 p CYCLE2 2
1 p CYCLE2 3
1 p CYCLE2 4
1 p CYCLE3 1
1 p CYCLE3 3
1 p CYCLE3 4
1 p CYCLE4 1
1 p CYCLE4 2
1 p CYCLE4 3
1 p CYCLE4 4
;

[Shmuel]

Try next not tested code:

proc sql noprint;
  create table tmp1 as
   select distinct id, trt, visit
   from have;
quit;
data tmp2;
 set tmp1;
     do extptnum=1 to 4; output; end;
run;
data want;
 merge have tmp2;
  by id trt visit;
run;

[PGStats]

Use a cross join to generate all needed combinations :

data have;
input id trt $ visit $ extptnum other $;
datalines;
1 p CYCLE1 1 a
1 p CYCLE1 2 b 
1 p CYCLE1 3 c
1 p CYCLE2 2 d 
1 p CYCLE2 3 e 
1 p CYCLE2 4 f
1 p CYCLE3 1 g
1 p CYCLE3 3 h
1 p CYCLE3 4 i
1 p CYCLE4 1 j
1 p CYCLE4 2 k
1 p CYCLE4 3 l
1 p CYCLE4 4 m
;

proc sql;
select 
    a.*, 
    b.*, 
    c.other
from
    (select unique id, trt, visit from have) as a cross join
    (select unique extptnum from have) as b left join 
    have as c on a.id=c.id and a.trt=c.trt and a.visit=c.visit and b.extptnum=c.extptnum;
quit;

id 	trt 	visit 	extptnum 	other
1 	p 	CYCLE1 	1 	a
1 	p 	CYCLE1 	2 	b
1 	p 	CYCLE1 	3 	c
1 	p 	CYCLE1 	4 	 
1 	p 	CYCLE2 	1 	 
1 	p 	CYCLE2 	2 	d
1 	p 	CYCLE2 	3 	e
1 	p 	CYCLE2 	4 	f
1 	p 	CYCLE3 	1 	g
1 	p 	CYCLE3 	2 	 
1 	p 	CYCLE3 	3 	h
1 	p 	CYCLE3 	4 	i
1 	p 	CYCLE4 	1 	j
1 	p 	CYCLE4 	2 	k
1 	p 	CYCLE4 	3 	l
1 	p 	CYCLE4 	4 	m

[Reeza]

Just use the SPARSE option on PROC FREQ.

proc freq data=have;
table id*trt*visit*pNum / sparse out=want;
run;

[s_lassen]

Here is a datastep solution:

data want;
  set have; 
  by id trt visit;
  if first.visit then 
    _extptnum=1;
  do extptnum=_extptnum to extptnum;
    output;
    end;
  _extptnum=extptnum; /* extptnum is now original value +1 */
  if last.visit;
  do extptnum=extptnum to 4;
    output;
    end;
  drop _extptnum;
  retain _extptnum;
run;

Note that this solution, unlike the one by @PGStats, also assigns values to other variables: 

• If it is not the last visit, holes will be filled out using data from the next higher extptnum value
• If it is the last visit, the values will be used to fill out up to extptnum=4