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

• SAS Tutorials
• Normalization in SAS
• SQL Aggregations & DataSet(Row & colummn operations) in SAS
• Working with Macro Variable in SAS
• SAS Interact With Oracle
• SAS Interview Questions & Answers