PHP Classes

SAS to MySQL: Extract data dictionaries from SAS file into MySQL

Recommend this page to a friend!
     
  Info   Example   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not yet rated by the usersTotal: 67 All time: 10,346 This week: 455Up
Version License PHP version Categories
sas2sql 1.0Freely Distributable5PHP 5, Databases, Files and Folders, P...
Description 

Author

Greg Neyman

This class can extract data dictionaries from SAS file into MySQL.

It can parse SAS files and extract values of data dictionaries (Format, Input, Label).

The class creates a table on MySQL database to insert the extracted data.

It replaces numeric data values with full text data.

The class can optimize data replacing VARCHAR default into appropriate INT or ENUM for easier data browsing. It also split tables along columns when there are too many columns during optimization.

Innovation Award
PHP Programming Innovation award nominee
January 2017
Number 12		 SAS is a programming language used for statistical analysis.

Its source code may include the definition of arrays of data, also known as data dictionaries.

This package can extract data dictionaries from SAS files into a MySQL database.

Manuel Lemos
Picture of Greg Neyman
  Performance   Level  
Name: Greg Neyman <contact>
Classes: 2 packages by
Country: United Kingdom
Age: 47
All time rank: 4207187 in United Kingdom
Week rank: 200 Up10 in United Kingdom Up

Level 1 Star

 Innovation award
Innovation award
Nominee: 2x

Example

<?php
/*
The definition files I've built this off of are ftp://ftp.cdc.gov/pub/Health_Statistics/NCHS/Dataset_Documentation/NHAMCS/sas
The data files are ftp://ftp.cdc.gov/pub/Health_Statistics/NCHS/Datasets/NHAMCS

Hilariously the public version of ed13for.txt has an error in it. This is good, it prompted me to write in error checking into the class.
*/
$link=mysqli_connect("localhost","root","","nhamcs") or die($link->error);
include("sas2sql.php");
$thingy= new sas2sql();
foreach(array("format"=>"ed14"."for.txt","input"=>"ed14"."inp.txt","label"=>"ed14"."lab.txt","data"=>"ED2014") as $k=>$v){
  $fh=fopen($v,"r");
  $thingy->$k=fread($fh,filesize($v));
  fclose($fh);
}
$thingy->data=trim($thingy->data);
$thingy->name="ed2014";
$thingy->comm=true;
$thingy->save="ed2014.sql";
$thingy->convert();
foreach(explode(";",$thingy->query) as $query) {
    if(!empty(trim($query))) $link->query($query) or die($link->error);
}
$result=$link->query($thingy->split_query());
foreach(explode(";",$thingy->split_results($result->fetch_all(),12)) as $query) {
    if(!empty(trim($query))) $link->query($query) or die($link->error);
}
$result=$link->query("SHOW TABLES WHERE `Tables_in_nhamcs` LIKE 'ed2014%'") or die($link->error);
$tables=$result->fetch_all();
foreach($tables as $table) {
    $result=$link->query($thingy->optimise_query($table[0]));
    foreach(explode(";",$thingy->optimise_results($result->fetch_all(),$table[0])) as $query) {
        if(!empty(trim($query))) $link->query($query) or die($link->error.$table[0]);
    }
}
?>

  Files folder image Files (2)  
File Role Description
Accessible without login Plain text file example.php Example Example Script
Plain text file sas2sql.php Class Class Source

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
Downloadsas2sql-2020-06-21.zip 5KB
Downloadsas2sql-2020-06-21.tar.gz
Install with ComposerInstall with Composer
 Version Control Unique User Downloads Download Rankings  
 0%
Total:67
This week:0
All time:10,346
This week:455Up

  Applications that use this package  
No pages of applications that use this class were specified.

Add link image If you know an application of this package, send a message to the author to add a link here.