1 / 26

Using MS-ACCESS® Metadata to Drive Automated SAS® Data Processing

Using MS-ACCESS® Metadata to Drive Automated SAS® Data Processing. Gary N. Weeks Centers for Disease Control Atlanta, Georgia. PRAMS Pregnancy Risk Assessment Monitoring System. Pregnancy and birth outcome data Grown to 32 participating agencies Mixed mode data collection

arien
Download Presentation

Using MS-ACCESS® Metadata to Drive Automated SAS® Data Processing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Using MS-ACCESS® Metadata to Drive Automated SAS® Data Processing Gary N. Weeks Centers for Disease Control Atlanta, Georgia

  2. PRAMSPregnancy Risk Assessment Monitoring System • Pregnancy and birth outcome data • Grown to 32 participating agencies • Mixed mode data collection • Unique data structures

  3. PRAMS Paticipatants

  4. PRAMS Participants

  5. Metadata in Microsoft Access database • Ease of maintenance • Updates incorporated immediately • Scaleable

  6. Scripting Automation • Read manifest file • Perform initial file management • Call SAS processing program

  7. Manifest SDN_POST datetime="7/5/2005 11:57:29 AM" Comment="" UserFilename="ME198.zip" type="upload" state="ME" batch_number="198" birth_year="2004“ email_address=“contact@maine.gov" . . .

  8. SAS Processing • Read flat files into SAS data sets • Check variable ranges • Validate skip patterns • Log Errors • Recode data values • Combine data • Generate and send processing reports

  9. Read Flat Files • 4 fixed format files • 1 state specific format file • Input using positional input information from metadata

  10. Basic Metadata Elements

  11. Filtered Metadata

  12. Put Metadata into Macro Variables proc sql noprint ; SELECT Variable, StartCol, Length, BCLabel, BCRange, BCRecode, Type INTO :Variable1-:Variable&NUM_OBS, :StartCol1-:StartCol&NUM_OBS, :Length1-:Length&NUM_OBS, :BCLabel1-:BCLabel&NUM_OBS, :BCRange1-:BCRange&NUM_OBS, :BCRecode1-:BCRecode&NUM_OBS, :Type1-:Type&NUM_OBS FROM bc_descrip; QUIT;

  13. %Let varRead= INPUT ; %DO I=1 %TO &NUM_OBS %By 1 ; %if &&Type&I =1 %then %Let LengthFt=&&Length&I...; %else %if &&Type&I =2 %then %Let LengthFt=$&&Length&I...; %Let varRead= &varRead @&&StartCol&I &&Variable&I &LengthFt; %END; data state.bc_&state&batch; infile MDATA missover; &varRead; run;

  14. Building An Input Statement %Let varRead= INPUT ; %DO I=1 %TO &NUM_OBS %By 1 ; %if &&Type&I =1 %then %Let LengthFt=&&Length&I...; %else %if &&Type&I =2 %then %Let LengthFt=$&&Length&I...; %Let varRead= &varRead @&&StartCol&I &&Variable&I &LengthFt; %END;

  15. &varRead INPUT @90 ADM_NNC 1. @79 BCOUNTY 2. @77 BW 1. @83 DD_MDOB 2. @87 INTERVAL 3. @81 MM_MDOB 2. @78 PAY 1. @85 YY_MDOB 2. @67 HISP_BC 1. @68 MM_HBP 1. @69 MM_BLEED 1. @70 MM_DIAB 1. @71 MM_FEVER 1. @72 MM_PROM 1. @73 MM_ABNOR 1. @74 MM_NOMD 1. @75 MM_NOLD 1. @76 POB 1. @1 MM_DOB 2. @3 DD_DOB 2. @5 YY_DOB 2. @7 MM_LMP 2. @9 DD_LMP 2. @11 YY_LMP 2. @13 PNC_MTH 2. @15 PNC_VST 2. @17 GRAM 4. @21 MARRIED 1. @22 SEX 1. @23 PLURAL 1. @24 MAT_AGE 2. @26 MAT_ED 1. @27 MAT_RACE $1. @28 PAT_ED 1. @29 MM_LLB 2. @31 YY_LLB 2. @33 PRE_LB 2. @35 GEST_WK 2. @37 MOMSMOKE 1. @38 MOMCIG 2. @40 MOMDRINK 1. @41 MOMDRKS 2. @43 MOMLBS 2. @45 DEFECT 1. @46 OTH_TERM 1. @47 DEL_VAG 1. @48 DEL_VCS 1. @49 DEL_1CS 1. @50 DEL_RCS 1. @51 DEL_FORC 1. @52 DEL_VACM 1. @53 MOM_RES 3. @56 HOSPITAL $3. @59 BC 8.

  16. &varRead (Human Version) INPUT @90 ADM_NNC 1. @79 BCOUNTY 2. @77 BW 1. @83 DD_MDOB 2. @87 INTERVAL 3. @81 MM_MDOB 2. @78 PAY 1. @85 YY_MDOB 2. . . .

  17. Create the Data Set data state.bc_&state&batch; infile MDATA missover; &varRead; run;

  18. Formats • Stored in metadata • Used to recode values • Read using the Cntlin option

  19. Formats

  20. Formats libname RcdFrmts access "J:\path\PRAMS_MetaData.mdb"; data RecodeFormats; set RcdFrmts.Recodeformats (drop=HLO); start=compress(start); end=compress(end); run; procsort data=RecodeFormats nodup; out=recodeFmts; by fmtname start end; run; procformat cntlin=recodeFmts; run;

  21. Skip Pattern Validation • Some survey questions may be unanswered • The questionnaire is incomplete • Skipped because previous a question makes it irrelevant

  22. Lead Question 24. Did you have any of these problems during your most recent pregnancy? a. High blood sugar (diabetes) that started before this pregnancy b. High blood sugar (diabetes) that started during this pregnancy c. Vaginal bleeding d. Kidney or bladder (urinary tract) infection e. Severe nausea, vomiting, or dehydration f. Cervix had to be sewn shut (incompetent cervix) g. High blood pressure, hypertension (including pregnancy-induced hypertension [PIH], preeclampsia, or toxemia) h. Problems with the placenta (such as abruptio placentae or placenta previa) i. Labor pains more than 3 weeks before my baby was due (preterm or early labor) j. Water broke more than 3 weeks before my baby was due (premature rupture of membranes [PROM]) k. I had to have a blood transfusion l. I was hurt in a car accident If you did not have any of these problems, go to Question 26.

  23. Follow Up 25. Did you do any of the following things because of these problems? a. I went to the hospital or emergency room and stayed less than 1 day b. I went to the hospital and stayed 1 to 7 days c. I went to the hospital and stayed more than 7 days d. I stayed in bed at home more than 2 days because of my doctor’s or nurse’s advice

  24. Skip Patterns • Validation • Error output specification

  25. Integrity Constraints proc datasets library=state nolist; modify ckskp1; ic create MORB5LT1217_skp = check (where=( (((MORB5LT1 = ._ and MORB_BLD in (1 )) or (MORB5LT1 ^= ._ and MORB_BLD not in (1 ))) and type=1) or (((MORB5LT1 = ._ and MORB_BLD in (1,8 )) or (MORB5LT1 ^= ._ and MORB_BLD not in (1,8 ))) and type=2) or (((MORB5LT1 = ._ and MORB_BP in (1 )) or (MORB5LT1 ^= ._ and MORB_BP not in (1 ))) and type=1) or (((MORB5LT1 = ._ and MORB_BP in (1,8 )) or (MORB5LT1 ^= ._ and MORB_BP not in (1,8 ))) and type=2) . . .

  26. Skip Pattern Validation

More Related