FOCUS AREAS

Return to ODS MARKUP

Base SAS

A Tagset to Generate SQL

A few of us ODS developers like to write programs in Python. Because of that, we are sometimes led down interesting paths. This tagset is a result of one of those paths.

We were working on a project that needed access to several data sets within SAS. These data sets changed infrequently and were also fairly small. For speed, efficiency, and independence, we decided to mirror the data in an SQL database called Gadfly. Gadfly is small, fast, and - most important - easy to access from Python code. The data was also small, so we completely recreated the database every night. It takes 30 seconds.

Using the SQL Tagset

The SQL tagset was designed to work with ODS and PROC PRINT. Using it is very simple. After you run the tagset, all you need is an ODS statement and a PROC PRINT. The sample PROC PRINT and the tagset are all included in a sample job.

   ods tagsets.sql file="class.sql";
   proc print data=sashelp.class ;
   run;

   ods _all_ close;

The resulting output file looks like this:

   Create table CLASS
          (Name varchar(7), Sex varchar(1), Age float, Height float, Weight float);

   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Alfred', 'M', 14, 69.0, 112.5);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Alice', 'F', 13, 56.5, 84.0);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Barbara', 'F', 13, 65.3, 98.0);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Carol', 'F', 14, 62.8, 102.5);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Henry', 'M', 14, 63.5, 102.5);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('James', 'M', 12, 57.3, 83.0);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Jane', 'F', 12, 59.8, 84.5);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Janet', 'F', 15, 62.5, 112.5);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Jeffrey', 'M', 13, 62.5, 84.0);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('John', 'M', 12, 59.0, 99.5);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Joyce', 'F', 11, 51.3, 50.5);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Judy', 'F', 14, 64.3, 90.0);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Louise', 'F', 12, 56.3, 77.0);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Mary', 'F', 15, 66.5, 112.0);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Philip', 'M', 16, 72.0, 150.0);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Robert', 'M', 12, 64.8, 128.0);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Ronald', 'M', 15, 67.0, 133.0);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('Thomas', 'M', 11, 57.5, 85.0);
   Insert into CLASS(Name, Sex, Age, Height, Weight)
          Values ('William', 'M', 15, 66.5, 112.0);

Changing the Data Types

If you don't like varchar data types or your database doesn't handle them, You can change the tagset. These type conversions are listed in a dictionary at the top of the tagset. You can edit them, or create a child tagset that over-rides the type_translations event. The event looks like this. It should be fairly obvious what you'll need to do.

      /* type translations */
      define event type_translations;
          set $types['string'] 'varchar';
          set $types['double'] 'float';
          set $types['int']    'integer';
      end;

Translating Column Names

The column names can also be translated. This is a global setting, so take care when using it. The only thing we wanted was to spell out "description." In the data sets, that column is consistently spelled as "desc". We change the name by creating a dictionary of data set column names with their corresponding SQL column name. You can add or change the translated names by redefining the name_translations event.

	  /* column name translation */
      define event name_translations;
          set $name_trans['desc'] 'description';
      end;

Conclusion

This tagset is much like the XBRL tagset, but simpler. It could be made to handle more complex data types, or to do updates instead of inserts. It could do all of those variations based on the value of tagset_alias.

We could add a lot of things. But this does what we need, and it is a great example of what tagsets are capable of. Maybe you will find it useful. At the very least it is a tagset worth learning from.

Of course we could have written a DATA step to do this very same thing. But I think tagsets are more flexible and reusable for things like this. You can decide which way is better for you.


Send mail to ods@sas.com with your comments.


Last Updated: Feb. 12, 2004