Merging variables in SAS
Interleaving concept:
To add 2 or more data set values into another new data set in sorting order.
Ex:
Data medi 1;
Input stno drug $ sdate date 9;
Format sdate date 9;
Cards;
110 study 12jan2003
120 placebo 12jan2003
119 study 13jan2003
;
Data medi2 ;
Input stno drug $ sdate date 9;
Format sdate date 9;
Cards;
102 placebo 15jan2003
115 study 16jan2003
130 placebo 17jan2003
;
/* concatenation*/
Data medicine1;
Set medi 1 medi 2;
Run;
Proc print data =medicine1;
Run;
/* interleaving*/
Proc sort data = medi1;
By drug ;
Run;
Proc sort data = medi2;
By drug ;
Run;
Data madicince 2;
Set medi1 medi2;
Run;
Proc print data =medicine2;
Run;
Concatenation and interleaving concept can be done by using with set tool or append procedure.
Inclined to build a profession as SAS Developer? Then here is the blog post on SAS Training
Merge statement:
Using merge statement we can combine data sets with relation and with out relation.
One to one merge without matching variable:
Ex:
Data one;
Input stno age;
Cards;
100 45
101 56
103 45
;
Data two;
Input gender $ weight ;
Cards;
Female 56
male 58
female 45
;
Data three;
Input color $;
Cards;
White
Black
White
;
Data demo;
Merge one two three;
Run; proc print data = demo;
Run;
One to one merge with matching variable:
In merge concept one to one relation and one to many relation working based on observations. Using merge option we can combine upto 14,000 tables.
Ex:
Data demo;
Input stno age gender $ weight ;
Cards;
100 45 Female 67
101 34 male 34
102 23 female 45
103 34 male 67
;
Data medi;
Input stno drug$ sdate :date 9.;
Format sdate date 9;
Cards;
102 col5mg 12oct2003
100 col5mg 15nov2003
103 col15mg16nov2003
101 col15mg14dec2003
;
Proc sort data = demo;
By stno ;
Run;
Proc sort data = medi;
By stno ;
Run;
Data demomedi;
Merge demo medi;
By stno;
Run;
Proc print data = demomedi;
Run;
Syntax:
Merge<data set name> <matching variable>
One to many merge without matching variable:
Ex:
Data demo;
Input stno age gender $ weight ;
Cards;
100 45 Female 67
101 34 male 34
102 23 female 45
103 34 male 67
;
Data lab ;
Input stno test $ units;
Cards;
100 hr 79
101 hr 78
102 hr 75
103 hr 76
100 sbp 179
101 sbp 178
102 sbp 175
103 sbp 145
100 dbp 89
101 dbp 88
102 dbp 85
103 dbp 89
;
Proc sort data = demo;
By stno ;
Run;
Proc sort data = lab;
By stno ;
Run;
Data demolab;
Merge demolab;
By stno;
Run;
Proc print data = demo lab;
Run;
Many to one merge without matching variable:
Ex:
Data demolab1;
Merge lab demo;
By stno;
Run;
Proc print data = demo lab1;
Run;
To run the merge concept based on matching and non matching observations:
/*To report only matching observations data who got expected adevents and their medicine information*/
c
203 col5mg 15jan2003 20jan2003
211 col10mg 15feb2003 18feb2003
178 col5mg 14mar2003 21mar2003
;
Data exadevent;
Input stno drug $ sdate edate;
Format sdate edate date 9;
Cards;
203 Eardis 19jan2003
211 Eyedis 16feb2003
;
Proc sort data = medi;
By stno ;
Run;
Proc sort data = lexadevent;
By stno ;
Run;
Data exadmedi;
Merge exadevent (in =var) medi;
By stno;
If var =1;
Run;
Proc print data = exadmedi;
Run;
/* To report non matching observations business requirements who do not get expected adevents and their medicine and lab information*/
Data exadmedi;
Merge exadevent (in =var) medi;
By stno;
If var =0;
Drop exad adsdate;
Run;
Proc print data = exadmedi;
Run;
Both datasets have matching and non matching observations to report required observations.
/* To report matching observation data who got expected and unexpected adverse vent*/
Ex:
Data exadevent;
Input stno exad $ sexdate :date 9.;
Format sevdate date 9;
Cards;
100 Eyedis 15oct2006
109 Eardis 16oct2006
145 Skinal 16oct2006
134 cold 14oct2006
;
Data unexadevent;
Input stno unexad $ unexdate :date 9.;
Format unexdate :date 9;
Cards;
123 comma 15oct2006
109 nervous pro 19oct2006
126 cordites 17oct2006
134 cold 18oct2006
;
Proc sort data = exadevent;
By stno ;
Run;
Proc sort data = unexadevent;
By stno ;
Run;
Data match;
Merge exadevent(in= var1)
Uncxadevent (in=var2);
By stno;
If var 1= 1 and var2=1;
Run;
Proc print data =match;
Run;
/* to report non matching variables who got expected and unexpected adevents*/
Data non match;
Merge exadevent(in= var1)
Un exadevent (in=var2);
By stno;
If var 1= 0 or var2=0;
Run;
Proc print data = non match;
Run;
/* To report non matching observation from the required data set who got only excepected adevents*/
Data non exad;
Merge exadevent
Un exadevent (in=var2);
By stno;
If var 2= 0 ;
Drop unexced un exsdate;
Run;
Proc print data = non exad;
Run;
/* to report non matching observation from the required data set who got only unexadevent */
Data nonunad;
Merge exadevent(in =var1)
Un exadevent ;
By stno;
If var 1= 0 ;
Drop exadsexsdate;
Run;
Proc print data = nonunad;
Run;
Many to many merge with matching variables:
Ex:
Data medi1;
Input pid visit $ drug $;
Cards;
100 week3 col5mg
101 week3 col10mg
100 week6 col5mg
101 week6 col10mg
102 week3 col15mg
102 week6 col15mg
102 week9 col15mg
;
Data medi2;
Input pid visit $ count;
Cards;
100 week3 2
101 week3 3
100 week6 4
101 week6 2
102 week3 2
102 week6 3
102 week9 5
;
Proc sort data =medi1;
By pid visit;
Run;
Proc sort data =medi2;
By pid visit;
Run;
Data medicine;
Merge medi1medi2;
By pid visit;
Run;
Proc print data = medicine;
Run;
For indepth knowledge on SAS, click on below