I want to add additional rows to DSN1 as in DSN2. How can get these rows using SAS? Thanks!

[San2018]

DSN1								DSN2
Id1	Id2	Month	Age	Crt_level	Crt_dt	Glu_level		Id1	Id2	Month	Age	Crt_level	Crt_dt	Glu_level
105	92	1	70	22	01/19/2017	122		105	92	1	70	22	01/19/2017	122
105	92	2	70	18	02/18/2017	118		105	92	2	70	18	02/18/2017	118
105	92	3	70	20	03/21/2017	120		105	92	3	70	20	03/21/2017	120
138	90	1	59	17	01/12/2017	117		138	90	1	59	17	01/12/2017	117
138	90	2	59	12	02/11/2017	112		138	90	2	59	12	02/11/2017	112
138	21	2	82	18	02/11/2017	118		138	90	3	59	Null	Null	.
138	21	3	82	12	03/30/2017	112		138	21	1	82	Null	Null	.
220	32	1	38	11	1/20/2017	111		138	21	2	82	18	02/11/2017	118
220	32	3	38	13	3/25/2017	113		138	21	3	82	12	03/30/2017	112
224	24	1	18	34	1/29/2017	134		220	32	1	38	11	1/20/2017	111
231	28	2	44	55	2/25/2017	155		220	32	2	38	Null	Null	.
231	34	3	55	22	3/1/2017	122		220	32	3	38	13	3/25/2017	113
								224	24	1	18	34	1/29/2017	134
								224	24	2	18	Null	Null	.
								224	24	3	18	Null	Null	.
								231	28	1	44	Null	Null	.
								231	28	2	44	55	2/25/2017	155
								231	28	3	44	Null	Null	.
								231	34	1	55	Null	Null	.
								231	34	2	55	Null	Null	.
								231	34	3	55	22	3/1/2017	122

[novinosrin]

data want;
set dsn1 dsn2(where=(Crt_dt='Null' and Crt_level='Null'));
by id1 id2 month;
run;

[San2018]

Hi  Novinosirin, 

Thanks for the code. I should have asked that I have Dsn1 and would like to have Dsn2. How to add additional missing rows with month and other columns to get as in Dsn2.

Thanks!

[novinosrin]

Can you help my eyesight by marking the additional rows that you want to add to dsn1 please?

[San2018]

Yes! Thanks!

[novinosrin]

Same as the 1st suggestion, correct me if this is not what you want

data dsn1;
input (Id1	Id2	Month	Age	Crt_level	Crt_dt 	Glu_level) ($);
cards;
105	92	1	70	22	01/19/2017	122
105	92	2	70	18	02/18/2017	118
105	92	3	70	20	03/21/2017	120
138	90	1	59	17	01/12/2017	117
138	90	2	59	12	02/11/2017	112
138	21	2	82	18	02/11/2017	118
138	21	3	82	12	03/30/2017	112
220	32	1	38	11	1/20/2017	111
220	32	3	38	13	3/25/2017	113
224	24	1	18	34	1/29/2017	134
231	28	2	44	55	2/25/2017	155
231	34	3	55	22	3/1/2017	122
;

data dsn2;
input (Id1	Id2	Month	Age	Crt_level	Crt_dt 	Glu_level) ($);
cards;
105	92	1	70	22	01/19/2017	122
105	92	2	70	18	02/18/2017	118
105	92	3	70	20	03/21/2017	120
138	90	1	59	17	01/12/2017	117
138	90	2	59	12	02/11/2017	112
138	90	3	59	Null	Null	.
138	21	1	82	Null	Null	.
138	21	2	82	18	02/11/2017	118
138	21	3	82	12	03/30/2017	112
220	32	1	38	11	1/20/2017	111
220	32	2	38	Null	Null	.
220	32	3	38	13	3/25/2017	113
224	24	1	18	34	1/29/2017	134
224	24	2	18	Null	Null	.
224	24	3	18	Null	Null	.
231	28	1	44	Null	Null	.
231	28	2	44	55	2/25/2017	155
231	28	3	44	Null	Null	.
231	34	1	55	Null	Null	.
231	34	2	55	Null	Null	.
231	34	3	55	22	3/1/2017	122
;
proc sort data=dsn1;
by id1 id2 month;
run;

proc sort data=dsn2;
by id1 id2 month;
run;


data want;
set dsn1 dsn2(where=(Crt_dt='Null' and Crt_level='Null'));
by id1 id2 month;
run;

proc print noobs;run;

Result Output:

SAS Output

Id1 Id2 Month Age Crt_level Crt_dt Glu_level

105	92	1	70	22	01/19/20	122
105	92	2	70	18	02/18/20	118
105	92	3	70	20	03/21/20	120
138	21	1	82	Null	Null
138	21	2	82	18	02/11/20	118
138	21	3	82	12	03/30/20	112
138	90	1	59	17	01/12/20	117
138	90	2	59	12	02/11/20	112
138	90	3	59	Null	Null
220	32	1	38	11	1/20/201	111
220	32	2	38	Null	Null
220	32	3	38	13	3/25/201	113
224	24	1	18	34	1/29/201	134
224	24	2	18	Null	Null
224	24	3	18	Null	Null
231	28	1	44	Null	Null
231	28	2	44	55	2/25/201	155
231	28	3	44	Null	Null
231	34	1	55	Null	Null
231	34	2	55	Null	Null
231	34	3	55	22	3/1/2017	122

[San2018]

No! But I don't have a dsn2 and need to create dsn2 using dsn1. I need to have 3 rows for each Id1,  Id2 with month and additional variables.  Thanks!

[novinosrin]

Oh got it, my bad. So sorry . Be right back after lunch

[novinosrin]

@San2018 Try and let me know:

data dsn1;
input Id1	Id2	Month	Age	Crt_level	Crt_dt :mmddyy10. 	Glu_leve1 $;
format Crt_dt mmddyy10.;
cards;
105	92	1	70	22	01/19/2017	122
105	92	2	70	18	02/18/2017	118
105	92	3	70	20	03/21/2017	120
138	90	1	59	17	01/12/2017	117
138	90	2	59	12	02/11/2017	112
138	21	2	82	18	02/11/2017	118
138	21	3	82	12	03/30/2017	112
220	32	1	38	11	1/20/2017	111
220	32	3	38	13	3/25/2017	113
224	24	1	18	34	1/29/2017	134
231	28	2	44	55	2/25/2017	155
231	34	3	55	22	3/1/2017	122
;

proc sort data=dsn1;
by id1 id2 month;
run;

data _null_;
if _n_=1 then do;
if 0 then set dsn1;
 dcl hash H (ordered: "A") ;
   h.definekey  ('id1','id2','month') ;
   h.definedata ('id1','id2','month','Age','Crt_level','Crt_dt','Glu_leve1') ;
   h.definedone () ;
end;
do _month=1 by 1 until(last.id2);
	set dsn1 end=last;
	by id1 id2 month;
	if first.id2 and last.id2 then 
		do;
			h.add();
			do __month=1 to 3;
			if __month ne month then do;
			month=__month;
			call missing(Crt_level,Crt_dt,Glu_leve1);
			if h.check() ne 0 then h.add();
			end;
			end;
		end;

	else if not last.id2 and  _month ne month then
		do;
			h.add();
			call missing(Crt_level,Crt_dt,Glu_leve1);
			month=_month;
			h.add();
	end;
else if not last.id2  and  _month eq month then h.add();
else if last.id2 and month ne _month and month ne 3 then 
	do;
		h.add();
		do __month=1 to 3;
		if month ne __month then do;
			call missing(Crt_level,Crt_dt,Glu_leve1);
			month=__month; if h.check() ne 0 then h.add();
			end;
		end;
	end;
else if  last.id2 and _month eq month and month ne 3 then
	do;
		h.add();
		do __month=1 to 3;
		if month ne __month then do;
			call missing(Crt_level,Crt_dt,Glu_leve1);
			month=__month; if h.check() ne 0 then h.add();
			end;
		end;
	end;
else if last.id2 and _month eq month and month=3 then h.add();
else if last.id2 and _month ne month and month=3 then
	do;
		h.add(); 
		do __month=1 to 3;
		if month ne __month then do;
			call missing(Crt_level,Crt_dt,Glu_leve1);
			month=__month; if h.check() ne 0 then h.add();
			end;
		end;
	end;
end;
if last then h.output(dataset:'want');
run;

[novinosrin]

data dsn1;
input Id1	Id2	Month	Age	Crt_level	Crt_dt :mmddyy10. 	Glu_leve1 $;
format Crt_dt mmddyy10.;
cards;
105	92	1	70	22	01/19/2017	122
105	92	2	70	18	02/18/2017	118
105	92	3	70	20	03/21/2017	120
138	90	1	59	17	01/12/2017	117
138	90	2	59	12	02/11/2017	112
138	21	2	82	18	02/11/2017	118
138	21	3	82	12	03/30/2017	112
220	32	1	38	11	1/20/2017	111
220	32	3	38	13	3/25/2017	113
224	24	1	18	34	1/29/2017	134
231	28	2	44	55	2/25/2017	155
231	34	3	55	22	3/1/2017	122
;


proc sort data=dsn1;
by id1 id2 month;
run;

data temp;
set dsn1(keep=Id1	Id2);
by Id1	Id2;
if first.id2;
do month=1 to 3;
output;
end;
run;
data want;
merge dsn1 temp;
by id1 id2 month;
run;

[novinosrin]

data dsn1;
input Id1	Id2	Month	Age	Crt_level	Crt_dt :mmddyy10. 	Glu_leve1 $;
format Crt_dt mmddyy10.;
cards;
105	92	1	70	22	01/19/2017	122
105	92	2	70	18	02/18/2017	118
105	92	3	70	20	03/21/2017	120
138	90	1	59	17	01/12/2017	117
138	90	2	59	12	02/11/2017	112
138	21	2	82	18	02/11/2017	118
138	21	3	82	12	03/30/2017	112
220	32	1	38	11	1/20/2017	111
220	32	3	38	13	3/25/2017	113
224	24	1	18	34	1/29/2017	134
231	28	2	44	55	2/25/2017	155
231	34	3	55	22	3/1/2017	122
;


proc sort data=dsn1 out=temp(keep=id1 id2) nodupkey;
by id1 id2 ;
run;

data want;
if _n_=1 then do;
if 0 then set dsn1;
  dcl hash H (dataset:'dsn1',ordered:"A") ;
   h.definekey  ("id1",'id2','month') ;
   h.definedata (all:'y') ;
   h.definedone () ;
   end;
   set temp;
do month=1 to 3;
if h.find() ne 0 then call missing(Crt_level,Crt_dt,Glu_leve1);
output;
end;
run;

[San2018 · Accepted solution]

Hi Novinosrin,

It works! Thank you very much!

[Andygray]

@San2018 It's highly inappropriate to mark your line as the correct and accepted answer. You should be marking one of @novinosrin 's solution as accepted answer and close the thread. That's the way to show courtesy

[San2018]

@Andygray  I am new. Is there anyway I can close/correct this thread to one of @novinosrin's solution?  I am new and sorry!

[novinosrin]

Hey no worries. Not a big deal. Chill and have fun!