Key-Value tables differ from typical analysis datasets as they do not have an own column for each variable. Key-value tables typically have one column that holds all variables values and a separate key variable that denotes the variable that is measured.

Person	Key	Value
1	Gender	Male
1	Age	37
2	Gender	Female
2	Age	25

This sample shows, how this data structure can be built from an analysis dataset that looks like:

Person	Gender	Age
1	Male	37
2	Female	22

Key-value tables are needed for association analysis (in this case we would concatenate Gender and Age to one variable) or in specific database and data warehouse structures.

This sample was authored by Gerhard Svolba. His book, Data Preparation for Analytics is available from the SAS Publishing online bookstore.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

SAS-Sample: Creating a Key-Value-Table
2007-12-06: Created by Gerhard Svolba

---

*** Optional, create an ID variable that enumerates the subjects;
DATA class; 
 SET sashelp.class;
 ID = _N_;
RUN;

*** Transpose data to a multiple-row-per-subject structure;
PROC TRANSPOSE DATA = class OUT = class_tp;
 BY ID name;
 VAR sex age height weight;
RUN;

*** Align, rename and contactenate values;
DATA Key_Value;
 SET class_tp;
 RENAME _name_ = Key; 
 Value = strip(col1);
 DROP col1;
 LABEL _name_ = "Key";
 FORMAT Feature $40.;
 Feature = catx("=",_name_,value);
RUN;

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

ID    Name       Key       Value    Feature

 1    Alfred     Sex       M        Sex=M
 1    Alfred     Age       14       Age=14
 1    Alfred     Height    69       Height=69
 1    Alfred     Weight    112.5    Weight=112.5
 2    Alice      Sex       F        Sex=F
 2    Alice      Age       13       Age=13
 2    Alice      Height    56.5     Height=56.5
 2    Alice      Weight    84       Weight=84
 3    Barbara    Sex       F        Sex=F
 3    Barbara    Age       13       Age=13
 3    Barbara    Height    65.3     Height=65.3
 3    Barbara    Weight    98       Weight=98
 4    Carol      Sex       F        Sex=F
 4    Carol      Age       14       Age=14
 4    Carol      Height    62.8     Height=62.8
 4    Carol      Weight    102.5    Weight=102.5
 5    Henry      Sex       M        Sex=M
 5    Henry      Age       14       Age=14
 5    Henry      Height    63.5     Height=63.5
 5    Henry      Weight    102.5    Weight=102.5
 6    James      Sex       M        Sex=M
 6    James      Age       12       Age=12
 6    James      Height    57.3     Height=57.3
 6    James      Weight    83       Weight=83

This tip shows how to create a key-value table with Proc Transpose and a SAS Datastep.

Type:

Sample

Date Modified:	2008-04-24 10:16:50
Date Created:	2008-03-25 15:01:51

Operating System and Release Information

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	Base SAS	z/OS
		OpenVMS VAX
		Microsoft® Windows® for 64-Bit Itanium-based Systems
		Microsoft Windows Server 2003 Datacenter 64-bit Edition
		Microsoft Windows Server 2003 Enterprise 64-bit Edition
		Microsoft Windows XP 64-bit Edition
		Microsoft® Windows® for x64
		OS/2
		Microsoft Windows 95/98
		Microsoft Windows 2000 Advanced Server
		Microsoft Windows 2000 Datacenter Server
		Microsoft Windows 2000 Server
		Microsoft Windows 2000 Professional
		Microsoft Windows NT Workstation
		Microsoft Windows Server 2003 Datacenter Edition
		Microsoft Windows Server 2003 Enterprise Edition
		Microsoft Windows Server 2003 Standard Edition
		Microsoft Windows XP Professional
		Windows Millennium Edition (Me)
		Windows Vista
		64-bit Enabled AIX
		64-bit Enabled HP-UX
		64-bit Enabled Solaris
		ABI+ for Intel Architecture
		AIX
		HP-UX
		HP-UX IPF
		IRIX
		Linux
		Linux for x64
		Linux on Itanium
		OpenVMS Alpha
		OpenVMS on HP Integrity
		Solaris
		Solaris for x64
		Tru64 UNIX
SAS System	SAS Enterprise Miner	z/OS
		Microsoft® Windows® for 64-Bit Itanium-based Systems
		Microsoft Windows Server 2003 Datacenter 64-bit Edition
		Microsoft Windows Server 2003 Enterprise 64-bit Edition
		Microsoft Windows XP 64-bit Edition
		Microsoft® Windows® for x64
		Microsoft Windows 95/98
		Microsoft Windows 2000 Advanced Server
		Microsoft Windows 2000 Datacenter Server
		Microsoft Windows 2000 Server
		Microsoft Windows 2000 Professional
		Microsoft Windows NT Workstation
		Microsoft Windows Server 2003 Datacenter Edition
		Microsoft Windows Server 2003 Enterprise Edition
		Microsoft Windows Server 2003 Standard Edition
		Microsoft Windows XP Professional
		Windows Millennium Edition (Me)
		Windows Vista
		64-bit Enabled AIX
		64-bit Enabled HP-UX
		64-bit Enabled Solaris
		ABI+ for Intel Architecture
		AIX
		HP-UX
		HP-UX IPF
		Linux
		Linux for x64
		Linux on Itanium
		Solaris
		Solaris for x64
		Tru64 UNIX