When Excel writes a file in the DIF format, SAS is unable to read the file. \u00a0Here\u2019s a valid DIF that has 24 columns, and 446,213 rows:<\/p>\n
TABLE\r\n0,1\r\n\"\"\r\nVECTORS\r\n0,24 \r\n\"\"\r\nTUPLES\r\n0,446213 \r\n<\/pre>\nNote that \u201cTuples\u201d in mathematical formulas are equivalent to \u201cRows\u201d. A VECTOR is like a dimension, or field. In the case of Excel, it refers to columns. So far so good. However here is how such a file would be saved by Excel 2010:<\/p>\n
TABLE\r\n0,1\r\n\"EXCEL\"\r\nVECTORS\r\n0,446213\r\n\"\"\r\nTUPLES\r\n0,24<\/pre>\n <\/p>\n
Excel has no problem reading the above file format, as it ignores tuples\/vectors internally. However SAS cannot handle this not-so-standard deviation.<\/p>\n
Below is VBA code that after saving a DIF file \u201cfixes\u201d the header by opening the file in binary mode and corrects the issue. Note FName contains both path and filename:<\/p>\n
\r\n 'Fixup TUPLES and VECTORS\r\n \r\nDim filenum As Integer\r\nDim strData As String\r\nDim VectorIdx As Integer\r\nDim TuplesIdx As Integer\r\nDim VectorStr As String\r\nDim TuplesStr As String\r\nConst CharsToProcess = 60\r\nDim outStr\r\nDim CRLF As String\r\nDim DoubleQuote As String\r\nDim Fname As String\r\n \r\nCRLF = Chr(13) & Chr(10)\r\nDoubleQuote = Chr(34)\r\n \r\nFname = saveString\r\n \r\nfilenum = FreeFile\r\nOpen Fname For Binary Access Read As filenum\r\n \r\nstrData = String$(CharsToProcess, \" \")\r\nGet #filenum, , strData\r\nClose #filenum\r\n \r\nVectorIdx = InStr(strData, \"VECTORS\")\r\nTuplesIdx = InStr(strData, \"TUPLES\")\r\nVectorStr = Mid(strData, VectorIdx + 9, 14) 'overly generous portion of chars\r\nTuplesStr = Mid(strData, TuplesIdx + 8, 14)\r\n \r\nIf InStr(TuplesStr, Chr(13)) > 0 Then 'trim CR LF\r\n TuplesStr = Left(TuplesStr, InStr(TuplesStr, Chr(13)) - 1)\r\nEnd If\r\n \r\nIf InStr(VectorStr, Chr(13)) > 0 Then 'trim CR LF\r\n VectorStr = Left(VectorStr, InStr(VectorStr, Chr(13)) - 1)\r\nEnd If\r\n \r\noutStr = \"VECTORS\" & CRLF & TuplesStr & CRLF & DoubleQuote & DoubleQuote & CRLF & \"TUPLES\" & CRLF & VectorStr\r\n \r\nfilenum = FreeFile\r\nOpen Fname For Binary Access Write As filenum\r\nPut #filenum, VectorIdx, outStr\r\nClose #filenum\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"When Excel writes a file in the DIF format, SAS is unable to read the file. \u00a0Here\u2019s a valid DIF that has 24 columns, and 446,213 rows: TABLE 0,1 “” VECTORS 0,24 “” TUPLES 0,446213 Note that \u201cTuples\u201d in mathematical formulas are equivalent to \u201cRows\u201d. A VECTOR is like a dimension, or field. In the […]<\/p>\n","protected":false},"author":8,"featured_media":2131,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[73,48],"tags":[],"class_list":["post-2130","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-analytics-and-reporting","category-vba"],"acf":[],"_links":{"self":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/2130","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/users\/8"}],"replies":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/comments?post=2130"}],"version-history":[{"count":1,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/2130\/revisions"}],"predecessor-version":[{"id":2133,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/2130\/revisions\/2133"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media\/2131"}],"wp:attachment":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media?parent=2130"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/categories?post=2130"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/tags?post=2130"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}