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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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

 

DSN1       DSN2      
Id1Id2MonthAgeCrt_levelCrt_dtGlu_level Id1Id2MonthAgeCrt_levelCrt_dtGlu_level
105921702201/19/2017122 105921702201/19/2017122
105922701802/18/2017118 105922701802/18/2017118
105923702003/21/2017120 105923702003/21/2017120
138901591701/12/2017117 138901591701/12/2017117
138902591202/11/2017112 138902591202/11/2017112
138212821802/11/2017118 13890359NullNull.
138213821203/30/2017112 13821182NullNull.
22032138111/20/2017111 138212821802/11/2017118
22032338133/25/2017113 138213821203/30/2017112
22424118341/29/2017134 22032138111/20/2017111
23128244552/25/2017155 22032238NullNull.
23134355223/1/2017122 22032338133/25/2017113
        22424118341/29/2017134
        22424218NullNull.
        22424318NullNull.
        23128144NullNull.
        23128244552/25/2017155
        23128344NullNull.
        23134155NullNull.
        23134255NullNull.
        23134355223/1/2017122

Accepted Solutions
Solution
‎05-08-2018 08:46 PM
Occasional Contributor
Posts: 6

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

Posted in reply to novinosrin

Hi Novinosrin,

 

It works! Thank you very much!

View solution in original post


All Replies
PROC Star
Posts: 1,815

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

[ Edited ]
data want;
set dsn1 dsn2(where=(Crt_dt='Null' and Crt_level='Null'));
by id1 id2 month;
run;
Occasional Contributor
Posts: 6

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

Posted in reply to novinosrin

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!

PROC Star
Posts: 1,815

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

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

Occasional Contributor
Posts: 6

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

Posted in reply to novinosrin

Yes! Thanks!

PROC Star
Posts: 1,815

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

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
105921702201/19/20122
105922701802/18/20118
105923702003/21/20120
13821182NullNull 
138212821802/11/20118
138213821203/30/20112
138901591701/12/20117
138902591202/11/20112
13890359NullNull 
22032138111/20/201111
22032238NullNull 
22032338133/25/201113
22424118341/29/201134
22424218NullNull 
22424318NullNull 
23128144NullNull 
23128244552/25/201155
23128344NullNull 
23134155NullNull 
23134255NullNull 
23134355223/1/2017122
Occasional Contributor
Posts: 6

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

Posted in reply to novinosrin

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!

PROC Star
Posts: 1,815

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

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

PROC Star
Posts: 1,815

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

[ Edited ]
Posted in reply to 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;
PROC Star
Posts: 1,815

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

Posted in reply to 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;
PROC Star
Posts: 1,815

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

Posted in reply to 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;
Solution
‎05-08-2018 08:46 PM
Occasional Contributor
Posts: 6

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

Posted in reply to novinosrin

Hi Novinosrin,

 

It works! Thank you very much!

Frequent Contributor
Posts: 75

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

@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

Occasional Contributor
Posts: 6

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

@Andygray  I am new. Is there anyway I can close/correct this thread to one of @novinosrin's solution?  I am new and sorry!
PROC Star
Posts: 1,815

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 211 views
  • 0 likes
  • 3 in conversation