topic Re: how to update a data set with more records in SAS Programming

how to update a data set with more records

knveraraju91 — Sat, 27 Jun 2020 20:53:36 GMT

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
;

Re: how to update a data set with more records

Shmuel — Sat, 27 Jun 2020 21:31:35 GMT

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;

Re: how to update a data set with more records

PGStats — Sat, 27 Jun 2020 22:07:42 GMT

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

Re: how to update a data set with more records

Reeza — Sun, 28 Jun 2020 02:03:07 GMT

Just use the SPARSE option on PROC FREQ.

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

Re: how to update a data set with more records

s_lassen — Mon, 29 Jun 2020 06:24:14 GMT

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