BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
San2018
Fluorite | Level 6

 

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
1 ACCEPTED SOLUTION

Accepted Solutions
14 REPLIES 14
novinosrin
Tourmaline | Level 20
data want;
set dsn1 dsn2(where=(Crt_dt='Null' and Crt_level='Null'));
by id1 id2 month;
run;
San2018
Fluorite | Level 6

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
Tourmaline | Level 20

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

novinosrin
Tourmaline | Level 20

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
San2018
Fluorite | Level 6

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
Tourmaline | Level 20

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

novinosrin
Tourmaline | Level 20

@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
Tourmaline | Level 20
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
Tourmaline | Level 20
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
Fluorite | Level 6

Hi Novinosrin,

 

It works! Thank you very much!

Andygray
Quartz | Level 8

@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
Fluorite | Level 6
@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
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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