Join Now

Juletip #23 Makro til konvertering af MySQL-script

by SAS Employee DanielKoch on ‎12-23-2014 05:43 AM (217 Views)

Hvis du har en datamodel liggende i MySQL Workbench, så er her (vedhæftet) en makro, der kan konvertere dens SQL-export til noget mere SAS-agigt.

%mysql_to_sas(

     mySqlFile = c:\mysql_to_sas\datamodel\datamodel.sql

     ,outputFolder = c:\mysql_to_sas\generated_macros

);

I output-mappen vil man få genereret makroerne %create_datasets(), %create_index() og %create_constraints() som alle har libname som parameter. Vedhæftet er også %delete_constraints(), der fjerner alle constraints i et libname eller en liste af datasets.

Foruden at gøre det lettere at oprette datamodellen i et eller flere SAS-miljøer, kan makroerne også være til gavn senere hen, hvis man f.eks. har behov for, at genoprette index. Det kan også være at ens data ikke understøtter constraints (f.eks. SPDE/SPDS), men så kan man lejlighedsvist teste integriteten, ved at lave en kopi af data i almindelige SAS-tabeller og efterfølgende køre %create_constraints(). Den vil så fejle fejle, hvis data ikke overholder de constraints man har defineret.

Et program (også vedhæftet) der bruger disse makroer kunne se således ud:

%let project_home             = c:\mysql_to_sas;

%let mysql_to_sas_macro       = &project_home.\macros\mysql_to_sas.sas;

%let delete_constraints_macro = &project_home.\macros\delete_constraints.sas;

%let mysql_file               = &project_home.\data_model\datamodel.sql;

%let macro_folder             = &project_home.\generated_macros;

%let data_folder              = &project_home.\data;

%let libname                  = data;

libname &libname. "&data_folder.";

%include "&mysql_to_sas_macro.";

%include "&delete_constraints_macro.";

*------------------------------------------------------------------------------*;

* Generate macros and include them in the program

*------------------------------------------------------------------------------*;

%mysql_to_sas(

      mySqlFile         = &mysql_file.

      ,outputFolder     = &macro_folder.

);

%include "&macro_folder.\create_datasets.sas";

%include "&macro_folder.\create_constraints.sas";

%include "&macro_folder.\create_index.sas";

*------------------------------------------------------------------------------*;

* Create the datasets

*------------------------------------------------------------------------------*;

%create_datasets(

      libname = &libname.

);

*------------------------------------------------------------------------------*;

* (Optionally) Add or import data e.g. PROC IMPORT, PROC SQL, DATA STEP etc.

*------------------------------------------------------------------------------*;

proc sql noprint;

      insert into &libname..Gender

            (id,name)

            values(1,'Female')

            values(2,'Male')

      ;

      insert into &libname..ChildStatus

            (id,name)

            values(1,'Naughty')

            values(2,'Nice')

      ;

      insert into &libname..GiftType

            (id,name)

            values(1,'Hard')

            values(2,'Soft')

      ;

      insert into &libname..Gift

            (id,name,type_fk,child_fk,assembled,wrapped,delivered)

            values(1,'Teddy Bear',2,2,'04dec2014:13:38'dt,'05dec2014:07:11'dt,'25dec2014:01:10'dt)

            values(2,'Race Car',1,1,'07dec2014:20:15'dt,'08dec2014:07:56'dt,'25dec2014:01:34'dt)

            values(3,'Blue Dress',2,4,'07dec2014:15:42'dt,'08dec2014:13:21'dt,'25dec2014:02:03'dt)

            values(4,'Action Figure',1,3,'11dec2014:12:50'dt,'12dec2014:10:34'dt,'25dec2014:02:17'dt)

      ;

      insert into &libname..Child

            (id,name,gender_fk,age,status_fk)

            values(1,'John',2,8,2)

            values(2,'Kelly',1,12,2)

            values(3,'Stewart',2,12,2)

            values(4,'Sussie',1,6,2)

            values(5,'Daniel',2,30,1)

      ;

quit;

*------------------------------------------------------------------------------*;

* Create index and constraints

*------------------------------------------------------------------------------*;

%create_constraints(

      libname = &libname.

);

%create_index(

      libname = &libname.

);

*------------------------------------------------------------------------------*;

* Delete constraints if you wish to run the program again

*------------------------------------------------------------------------------*;

/*

%delete_constraints(

      libname = &libname.

);

*/

Attachment