schema.sas

Here is the code for schema.sas

libname pwd '.';

* Edit the following to refer to the table of interest;
%let db=compq.;
%let tablename=fundq;
%let suffix=_schema;
%let filetype=.csv;
%let outcsv=&tablename&suffix&filetype;

* Use PROC CONTENTS to extract the information desired.;
proc contents data=&db&tablename out=schema noprint;
run;

* Do some preprocessing of the results;
data schema;
   set schema; * (keep=name format formatl formatd);
  format postgres_type $36.;
  if formatl=0 then formatl = length;
  if format="YYMMDDN" then postgres_type="date";
  else if type=1 then do;
    if formatd ^= 0 then postgres_type = "numeric(" || 
      trim(left(formatl)) || "," || trim(left(formatd)) || ")";
    if formatd = 0 and formatl ^= 0 then postgres_type = "integer";
    if formatd = 0 and formatl =0 then postgres_type = "float8";
  end;
  else if type=2 then postgres_type = "char(" || 
    trim(left(formatl)) || ")";
run;

* Now dump it out to a CSV file;
data _null_;
  %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
  %let _EFIREC_ = 0;     /* clear export record count macro
                          ! variable */
  file "&outcsv" delimiter=',' DSD DROPOVER lrecl=32767;
  set schema(keep=name postgres_type)   end=EFIEOD;
  format NAME $32. ;
  format postgres_type $36. ;
  do;
    EFIOUT + 1;
    put NAME $ @;
    put postgres_type $ ;
  end;
  if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR
        ! detection macro variable */
  if EFIEOD then call symputx('_EFIREC_',EFIOUT);
run;
Advertisements
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

One Response to schema.sas

  1. Pingback: Getting WRDS data into MySQL or PostgreSQL | iangow

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s