[SAS课件]-斯坦福大学sas教学课件SAS-SQL.pptVIP

HRP 223 – 2005 Topic 9 - Working with Multiple Tables Combining Data (1) When you have data sets that need to be combined you can do six different things: concatenating interleaving one to one table reading one to one table matching matched merging Updating These things can be done with data steps or with proc sql. I use and recommend SQL but I will show you the SAS way so you can read other people’s code. Combining Data (2)Concatenating *typical method; data all_dx; set dxyr1 dxyr2; run; *faster method; proc append base=dxyr1 data=dxyr2; run; Combining Data (3)Interleaving data all_dx; set dxyr1 dxyr2; by dxyear; run; Combining Data (4)One-to-one Read or Merge data datetype; set dxyr1; set cancer; run; Combining Data (5)Match Merge data new; merge pers_dat can_dat; by id; run; Combining Data (6)Updating data new; update birth_dx theup; by id; run; Database Reality Since the 1980’s, the dominant model for databases has been “relational.” Relational databases use multiple tables to store data. Each of these tables, which look just like spreadsheet pages, stores related information. The primary goals in laying out a relational database are to remove redundancy and extra variables. This process is called normalization. Normalization In a normalized database there is always a variable (or set of variables) that can be used to link the tables, but other than that, the same information is not repeated in a table or across tables. This buys you lots of space and speed! A Normalized Database Getting Data Out of A RDB! The dominant language for manipulating data in relational databases is called SQL, which is short for Structured Query Language. Pronounced sequel or Ess-Que-Ell SAS has a well implemented version of SQL and I use it to combine my tables. You can directly access other databases like Oracle, Sybase, MS Access and MS FoxPro. PROC SQL (1) The syntax is a bit different than the rest of SAS because it is SQL not SAS! proc sql; select VARI