Date:         Fri, 12 Jun 2009 18:41:19 -0700
Reply-To:     Savian <savian.net@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Savian <savian.net@GMAIL.COM>
Organization: http://groups.google.com
Subject:      Re: How to read user-defined formats using IOM data provider
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

On Jun 12, 1:56 pm, Jen <jliu1...@yahoo.com> wrote:
> Hi,
>
> I need to use IOMprovider (sql) to access the values of a user-defined
> formats.  It seems "dictionary.columns" gives the variables of a data
> table.  Is there something equivalent for the formats?
>
> For example, format $SEX has two values "male" and "female", how do I
> get them?
>
> Thanks in advance,
> Jenn

Here is what I use (C#):

        /// <summary>
        /// Gets a SAS dataset from the specified location but using
the SAS formats for the results. Only supported
        /// Base SAS formats will be handled. If a user-defined format
is encountered, the method will fail.
        /// </summary>
        /// <param name="sasLibrary">The physical location of the SAS
library to read in the data </param>
        /// <param name="sasDataSet">The name of the SAS dataset to
read</param>
        /// <param name="formatting">Whether all formats should be
applied to a SAS dataset</param>
        /// <returns>.NET datatable. All values returned will be
strings.</returns>
        public DataTable GetFormattedDataSet(string sasLibrary, string
sasDataSet, Formats formatting)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            if (formatting == Formats.None)
            {
                dt = GetDataSet(sasLibrary, sasDataSet);
            }
            else
            {
                try
                {
                    ADODB.Recordset set = new ADODB.Recordset();
                    ADODB.Connection conn = new ADODB.Connection();
                    string formats = "_ALL_";

                    if (formatting == Formats.SasSupported)
                        formats = CreateFormatString(sasLibrary,
sasDataSet);

                    conn.Open(@"Provider=sas.LocalProvider;Data Source
=" + sasLibrary, "", "", 0);
                    set.ActiveConnection = conn;
                    set.Properties["SAS Formats"].Value = formats;
                    set.Open(sasDataSet, Missing.Value,
ADODB.CursorTypeEnum.adOpenForwardOnly,
ADODB.LockTypeEnum.adLockReadOnly, (int)
ADODB.CommandTypeEnum.adCmdTableDirect);

                    OleDbDataAdapter da = new OleDbDataAdapter();

                    da.Fill(dt, set);
                    dt.TableName = "OUTDATA";
                    conn.Close();
                    //DataColumn dc = ds.Tables[0].Columns["date"];
                }
                catch (Exception ex)
                {
                    HandleError(MethodBase.GetCurrentMethod().Name,
ex);
                    return null;
                }
            }
            return dt;
        }