Using XML Mapper and XMLMAP to Read Data Documented by Data Documentation Initiative (DDI) Files

1. Using XML Mapper and XMLMAP to Read Data Documented by Data Documentation Initiative (DDI) Files Larry Hoyle Policy Research Institute University of Kansas

2. Read2825.sas 2825.xml DDI.map Work.ICPSR2825Household Work.ICPSR2825Family Work.ICPSR2825Person Da2825.txt Overview • A SAS program reads an XML metadata file and writes a SAS program to read the raw data file described by the metadata file. makeReader.sas Read2825.sas

3. DDI • “an international effort to establish a standard for technical documentation describing social science data” - http://www.icpsr.umich.edu/DDI/index.html

4. DDI Files • XML • DTD - http://www.icpsr.umich.edu/DDI/users/dtd/index.html • Metadata about: • The DDI file itself • The study that collected the data • The data file • Variables within the data file • Other Material

5. The Minimal DDI File <?xml version="1.0"?> <codeBook> <stdyDscr> <citation> <titlStmt> <titl>Howdy World: Valid but Useless Metadata</titl> </titlStmt> </citation> </stdyDscr> </codeBook>

6. Real Example: ICPSR 6084 Raw Data File 100001 161132146211115555299 9991199 99911219 200001 49992 30000102534 000325222641942 3834101202 100002 12213112421111212112222221121 2122 12 200002 12221 30000202574 000756221622052 4261103202

7. ICPSR 6084 – About The Study <citation> <titlStmt> <titl>CBS News Monthly Poll #2, August 1992</titl>

8. ICPSR 6084 – About The File <dimensns> <caseQnty>1,546</caseQnty> <varQnty>70</varQnty> <logRecL>80</logRecL> <recPrCas>3</recPrCas>

9. ICPSR 6084 – Reading The File With SAS <dimensns> <caseQnty>1,546</caseQnty> <varQnty>70</varQnty> <logRecL>80</logRecL> <recPrCas>3</recPrCas> <recNumTot>4,638</recNumTot> </dimensns> infile 'C:\DDRIVE\data\icpsr\data\6084\da6084.txt' LRECL=80 PAD;

10. More From ICPSR 6084 – first variable

11. input #1 cardno 1-1 More From ICPSR 6084 – Reading the first variable

12. More From ICPSR 6084 – another variable #3 respno 2-6

13. The Tasks • Pull the necessary information from a hierarchical xml file into SAS as tables • Use XML libname engine with an XMLMAP file • Use that information in SAS to read the raw data file

14. Making the XMLMAP File – SAS XML Mapper

15. Defining Tables –What Defines Rows Drag the element that defines rows to the root of the XMLMAP structure

16. Defining Tables –Row Defined

17. Defining Tables – What Defines Columns Drag an element that defines a column to the root of the table

18. Defining Tables – Column Defined

19. Viewing The XMLMap File

20. Viewing The XMLMap File – Row Path

21. Viewing The XMLMap File - Column Path

22. Viewing Sample SAS Code

23. Previewing the Table

24. XMLMapper Limitations • Not every XML file will have all the elements of any possible XML file of that type. • Use XML Schema instead of XML file • An XML Schema file may not work • XML file type defined by DTD • XML Schema too complex for XML Mapper

25. What then • You can use XMLMapper to start and then hand edit the XML MAP file.

26. DATADSCR_VAR DATADSCR_VARGRP DATADSCR_VAR_CATGRY DATADSCR_VAR_INVALRNG DATADSCR_VAR_INVALRNG_ITEM DATADSCR_VAR_INVALRNG_RANGE DATADSCR_VAR_VALRNG_ITEM DATADSCR_VAR_VALRNG_RANGE DOCDSCR_CITATION__AUTHENTY DOCDSCR_CITATION__COPYRIGHT DOCDSCR_CITATION__IDNO DOCDSCR_CITATION__OTHID DOCDSCR_CITATION__PRODDATE DOCDSCR_CITATION__PRODUCER DOCDSCR_CITATION__TITL FILEDSCR_FILETXT FILEDSCR_FILETXT_RECGRP STDYDSCR_CITATION_BIBLCIT STDYDSCR_CITATION_TITLSTMT STDYDSCR_CITATION_VERSTMT STDYDSCR_CITATION__AUTHENTY STDYDSCR_CITATION__COPYRIGHT STDYDSCR_CITATION__DISTRBTR STDYDSCR_CITATION__FUNDAG STDYDSCR_CITATION__GRANTNO STDYDSCR_CITATION__PRODDATE STDYDSCR_CITATION__PRODUCER STDYDSCR_CITATION__SOFTWARE STDYDSCR_METHOD__COLLMODE STDYDSCR_METHOD__DATACOLLECTOR STDYDSCR_METHOD__FREQUENC STDYDSCR_METHOD__RESINSTRU STDYDSCR_METHOD__SAMPPROC STDYDSCR_METHOD__TIMEMETH STDYDSCR_METHOD__WEIGHT STDYDSCR_STDYINFO_ABSTRACT STDYDSCR_STDYINFO__ANLYUNIT STDYDSCR_STDYINFO__COLLDATE STDYDSCR_STDYINFO__DATAKIND STDYDSCR_STDYINFO__GEOGCOVER STDYDSCR_STDYINFO__KEYWORD STDYDSCR_STDYINFO__NATION STDYDSCR_STDYINFO__TIMEPRD STDYDSCR_STDYINFO__TOPCCLAS STDYDSCR_STDYINFO__UNIVERSE Lots of Tables From DDI Mostly for Comments

27. data _null_; file reader lrecl=1024 ; length vEdited $ 2000; set DDIfile.stdyDscr_citation_titlStmt; if _n_=1 then put '/*' / ' SAS program to read ' agency ' ' IDNo ; stdyDscrTitl= compbl(tranwrd(translate(stdyDscrTitl, ' ', '09'x), '*/', '*_/')); put 'Study Title' _n_ ': ' stdyDscrTitl; altTitl=compbl(tranwrd(translate(altTitl, ' ','09'x),'*/','*_/')); put ' ' altTitl; /* SAS program to read ICPSR 6084 Study Title1 : CBS News Monthly Poll #2, August 1992 August National Poll II, Republican National Convention Write a SAS Program – Metadata Comment

28. data makeTheFormats; input fmtname $ 1-7 type $ 9-9 start $ 11-26 default 28-35 / label :&$512.; datalines; V00006f N 1 1 Yes V00006f N 3 1 Converted Refusal ; run; proc format cntlin=makeTheFormats; run; Cntlin file for Formats

29. Input • Logic to produce different input statements for: • Fixed column data • Delimited data

30. Multiple datasets if different record types Separate keep dataset options Logic to output to appropriate dataset if left(_RecordSetIdentifier) eq left("2 ") then DO; output ICPSR2825FAMILY ; END; Output if "&fileStructureType" eq "hierarchical" then do; put 'if left(_RecordSetIdentifier) eq left("' catValu '") then DO;' / " output " safeAgency +(-1) safeIDNo +(-1) rectype ';' / 'END;' //; end;

31. Some of the Limitations • DDI can describe nCubes, geographic coverage, variable groups • Current makeReader.sas can’t handle these • DDI definition includes recursive elements • E.g. recGrps within recGrps • Current makeReader,sas would not find nested elements

32. Questions?

33. Larry Hoyle Associate Scientist Policy Research Institute, University of Kansas 1541 Lilac Lane Lawrence, KS 66044-3177 LarryHoyle@ku.edu About the Speaker