1 / 100

Easy Come, Easy Go — Interactions between the DATA Step and External Files

Easy Come, Easy Go — Interactions between the DATA Step and External Files. Andrew T. Kuligowski, The Nielsen Company. Interactions … External Files Introduction. Introduction. Defining the external data source. Describing the appearance of the data. Practical Application. Conclusion.

marlon
Download Presentation

Easy Come, Easy Go — Interactions between the DATA Step and External Files

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. Easy Come, Easy Go — Interactions between the DATA Step and External Files Andrew T. Kuligowski, The Nielsen Company

  2. Interactions … External FilesIntroduction • Introduction • Defining the external data source • Describing the appearance of the data • Practical Application • Conclusion Interactions Between the DATA Step and External Files

  3. Interactions … External FilesIntroduction This presentation will provide basic answers to two questions: • How do I get my data into SAS for analysis? • How do I get my data out of SAS? Interactions Between the DATA Step and External Files

  4. Interactions … External FilesIntroduction Process of moving data from an external file to SAS, and from SAS to an external source, are mirror images of each other. (Almost) Interactions Between the DATA Step and External Files

  5. Interactions … External FilesIntroduction You need to know the answer to two questions in order to code properly. • Where is this external source / destination? (and, is there anything significant about this file?) • What does the data look like coming in / going out? Interactions Between the DATA Step and External Files

  6. Interactions … External FilesIntroduction "The time has come," the Walrus said,"To talk of many things:Of shoes--and ships--and sealing-wax--Of cabbages--and kings--And why the sea is boiling hot--And whether pigs have wings." Lewis Carroll "I am the Walrus." John Lennon Interactions Between the DATA Step and External Files

  7. Interactions … External FilesDefining the External Data Source Two statements that will identify an external file to DATA step (and possibly provide a little information about its form). • INFILE statement • FILE statement Interactions Between the DATA Step and External Files

  8. Interactions … External FilesDefining the External Data Source Most straightforward usage – hardcode the file name right in the statement. DATA SAMPLE ; /* full file name - under Windows */ INFILE 'c:\sasconf\sasconf.dat'; Self-documenting code! PLUS Requires coding change to point to a different dataset. MINUS Interactions Between the DATA Step and External Files

  9. Interactions … External FilesDefining the External Data Source Variation – use a macro variable. %LET FILENM = c:\sasconf\sasconf.dat;DATA SAMPLE ;/* macro file name - under Windows */ INFILE “&FILENM”; Of course, this isn’t much more flexible. However, with a little creative coding, this approach can be madevery flexible. Interactions Between the DATA Step and External Files

  10. Interactions … External FilesDefining the External Data Source More flexibility with FILEVAR statement. %LET FileNm = %STR(SASCONF); DATA SAMPLE ; RETAIN fn “&FileNm" ; File_in = TRIM( fn ) || ".dat" ; File_out = TRIM( fn ) || ".out" ; INFILE dummy FILEVAR=File_in ; FILE dummy2 FILEVAR=File_out ; Can be hardcoded, or it can change from obs to obs. Interactions Between the DATA Step and External Files

  11. Interactions … External FilesDefining the External Data Source More flexibility with FILEVAR statement. %LET FileNm = %STR(SASCONF); DATA SAMPLE ; RETAIN fn “&FileNm" ; File_in = TRIM( fn ) || ".dat" ; File_out = TRIM( fn ) || ".out" ; INFILE dummy FILEVAR=File_in ; FILE dummy2 FILEVAR=File_out ; Set to “dummy” to show these are not actually used by the routine when FILEVAR used, but different from each other to show they must be unique. Can be hardcoded, or it can change from obs to obs. File Reference Interactions Between the DATA Step and External Files

  12. Interactions … External FilesDefining the External Data Source Defining a File Reference • In the Operating System: //SAMPDATA DD DSN=SAS.GLOBAL.FORUM.SAMPDAT,// DISP=SHR Example uses MVS – shows why File References are also called “DDNAMES”. Interactions Between the DATA Step and External Files

  13. Interactions … External FilesDefining the External Data Source Defining a File Reference • Using the FILENAME statement: FILENAME confdata 'c:\sasconf\sasconf.dat'; Example uses Windows. Interactions Between the DATA Step and External Files

  14. Interactions … External FilesDefining the External Data Source Defining a File Reference • Using the FILENAME statement(2a) Special options for FILENAME: FILENAME <fileref>CLEAR;Remove / release specified File Ref. FILENAME <fileref>LIST;Print a list of active File Refs to SASLOG. Interactions Between the DATA Step and External Files

  15. Interactions … External FilesDefining the External Data Source Defining a File Reference • Using the FILENAME statement(2a) Special options for FILENAME:(2ai) Special pseudo File Reference: FILENAME _ALL_ LIST;FILENAME _ALL_ CLEAR;Perform action on all active File Refs Interactions Between the DATA Step and External Files

  16. Interactions … External FilesDefining the External Data Source Defining a File Reference • Special “Aggregate File Reference”MVS: Partition Dataset FILENAME confdata'userid.sasconf.data'; DATA SAS_Conf07; INFILE confdata(sascon07); Interactions Between the DATA Step and External Files

  17. Interactions … External FilesDefining the External Data Source Defining a File Reference • Special “Aggregate File Reference”MVS: Partition DatasetUnix: DirectoryWindows: Directory FILENAME confdata 'c:\sasconf'; DATA SAS_Conf07; INFILE confdata(sascon07); Interactions Between the DATA Step and External Files

  18. Interactions … External FilesDefining the External Data Source Other related functions … FILEREF RetCd2 = FILEREF( "confdat2" ); Return Code Meaning 0 File Reference is assigned >0 (+) File Reference not currently assigned <0 (-) File Reference assigned, but file it refers to does not (currently) exist. Interactions Between the DATA Step and External Files

  19. Interactions … External FilesDefining the External Data Source Other related functions … FILEEXIST IF FILEEXIST("c:\sasconf\sasconf2.dat") Return Code Meaning 0 External File does not exist 1 External File does exist Interactions Between the DATA Step and External Files

  20. Interactions … External FilesDefining the External Data Source Other related functions … FEXIST IF FEXIST("confdat2" ) THEN DO; Return Code Meaning 0 External File does not exist 1 External File does exist Interactions Between the DATA Step and External Files

  21. Interactions … External FilesDefining the External Data Source Specialized File Ref. – Input Data Only DATALINES • Data can be found instream at end of DATA step, denoted by DATALINES statement. • Instream data concluded by semicolon. Interactions Between the DATA Step and External Files

  22. Interactions … External FilesDefining the External Data Source Specialized File Ref. – Input Data Only DATALINES DATA SAS_Conf07; INFILE DATALINES; /* optional for DATALINES */ INPUT @ 1 Start_Dt MMDDYY8. @ 10 End_Dt MMDDYY8. @ 19 ConfName $CHAR16. @ 36 ConfLoc $CHAR16. ;DATALINES;04/16/07 04/19/07 SAS Global Forum Orlando FL09/16/07 09/18/07 PNWSUG Seattle WA09/30/07 10/02/07 SCSUG Austin TX11/04/07 11/06/07 SESUG Hilton Head SC ; Interactions Between the DATA Step and External Files

  23. Interactions … External FilesDefining the External Data Source Specialized File Ref. – Input Data Only DATALINES • Assumed to becard-image (80 bytes) • Can override with systemoption NOCARDIMAGE. Interactions Between the DATA Step and External Files

  24. Interactions … External FilesDefining the External Data Source Specialized File Ref. – Input Data Only DATALINES • Alias = CARDS. • Can also use DATALINES4(or CARDS4) if data mightcontain semicolon. Interactions Between the DATA Step and External Files

  25. Interactions … External FilesDefining the External Data Source Specialized File Ref. – Input Data OnlyDATALINES vs. CARDS 18 DATA _NULL_;19 SET SAS_Conf07;20 FILE CARDS;NOTE: The file CARDS cannot be opened for UPDATE processing.21 PUT _N_= ConfName ;22 RUN;NOTE: The SAS System stopped processing this step because of errors. Interactions Between the DATA Step and External Files

  26. Interactions … External FilesDefining the External Data Source Specialized File Ref. – Input Data OnlyDATALINES vs. CARDS 62 DATA _NULL_;63 SET SAS_Conf07;64 FILE DATALINES;65 PUT _N_= ConfName ;66 RUN;_N_=1 SAS Global Forum… … … … _N_=8 NESUGNOTE: There were 8 observations read from the data set WORK.SAS_CONF07. Where’s the error message? Interactions Between the DATA Step and External Files

  27. Interactions … External FilesDefining the Appearance of the Data Two questions (from early in this talk): Where is the data / where is it going? What does the data look like? 5 types of INPUT and PUT, although SAS only counts 4 of them as separate types.  • LIST input • COLUMN input • FORMATTED input • NAMED input • NULL input Interactions Between the DATA Step and External Files

  28. Interactions … External FilesDefining the Appearance of the Data • Two questions (from early in this talk): Where is the data / where is it going? • What does the data look like? • 5 types of INPUT and PUT, although SAS only counts 4 of them as separate types. Most references in presentation will be to INPUT for brevity’s sake, although most are also valid for PUT. • LIST input • COLUMN input • FORMATTED input • NAMED input • NULL input Interactions Between the DATA Step and External Files

  29. Interactions … External Files Defining the Appearance of the Data LIST Input (and Output) • Simply insert variables instatement. • No descriptive informationnecessary (except “$”denoting character vars.) • Blank space separates values. Interactions Between the DATA Step and External Files

  30. Interactions … External Files Defining the Appearance of the Data LIST Input (and Output)Example of non-working code DATA SAS_Conf07; INFILE DATALINES ; INPUT Start_Dt End_Dt ConfName $ ConfLoc $ ;DATALINES;04/16/07 04/19/07 SAS Global Forum Orlando FL06/03/07 06/06/07 PharmaSUG Denver CO09/16/07 09/18/07 PNWSUG Seattle WA09/30/07 10/02/07 SCSUG Austin TX10/17/07 10/19/07 WUSS San Francisco CA10/28/07 10/30/07 MWSUG Des Moines IA11/04/07 11/06/07 SESUG Hilton Head SC11/11/07 11/14/07 NESUG Baltimore MD; PROBLEM: Dates have specialized formats. Interactions Between the DATA Step and External Files

  31. Interactions … External Files Defining the Appearance of the Data LIST Input (and Output)Example of non-working code DATA SAS_Conf07; INFORMAT Start_Dt End_Dt mmddyy8. ; INFILE DATALINES ; INPUT Start_Dt End_Dt ConfName $ ConfLoc $ ; DATALINES; 04/16/07 04/19/07 SAS Global Forum Orlando FL 06/03/07 06/06/07 PharmaSUG Denver CO 09/16/07 09/18/07 PNWSUG Seattle WA 09/30/07 10/02/07 SCSUG Austin TX 10/17/07 10/19/07 WUSS San Francisco CA 10/28/07 10/30/07 MWSUG Des Moines IA 11/04/07 11/06/07 SESUG Hilton Head SC 11/11/07 11/14/07 NESUG Baltimore MD ; SOLUTION: Use INFORMAT statement. Interactions Between the DATA Step and External Files

  32. Interactions … External Files Defining the Appearance of the Data LIST Input (and Output)Example of non-working code DATA SAS_Conf07; INFORMAT Start_Dt End_Dt mmddyy8. ; INFILE DATALINES ; INPUT Start_Dt End_Dt ConfName $ ConfLoc $ ; DATALINES; 04/16/07 04/19/07 SAS Global Forum Orlando FL 06/03/07 06/06/07 PharmaSUG Denver CO 09/16/07 09/18/07 PNWSUG Seattle WA 09/30/07 10/02/07 SCSUG Austin TX 10/17/07 10/19/07 WUSS San Francisco CA 10/28/07 10/30/07 MWSUG Des Moines IA 11/04/07 11/06/07 SESUG Hilton Head SC 11/11/07 11/14/07 NESUG Baltimore MD ; PROBLEM: Values longer than 8 characters. Interactions Between the DATA Step and External Files

  33. Interactions … External Files Defining the Appearance of the Data LIST Input (and Output)Example of non-working code DATA SAS_Conf07; INFORMAT Start_Dt End_Dt mmddyy8. ; LENGTH ConfName $ 16 ConfLoc $ 16. ; INFILE DATALINES ; INPUT Start_Dt End_Dt ConfName $ ConfLoc $ ; DATALINES; 04/16/07 04/19/07 SAS Global Forum Orlando FL 06/03/07 06/06/07 PharmaSUG Denver CO 09/16/07 09/18/07 PNWSUG Seattle WA 09/30/07 10/02/07 SCSUG Austin TX 10/17/07 10/19/07 WUSS San Francisco CA 10/28/07 10/30/07 MWSUG Des Moines IA 11/04/07 11/06/07 SESUG Hilton Head SC 11/11/07 11/14/07 NESUG Baltimore MD ; SOLUTION: UseLENGTH statement. Interactions Between the DATA Step and External Files

  34. Interactions … External Files Defining the Appearance of the Data LIST Input (and Output)Example of non-working code DATA SAS_Conf07; INFORMAT Start_Dt End_Dt mmddyy8. ; LENGTH ConfName $ 16 ConfLoc $ 16. ; INFILE DATALINES ; INPUT Start_Dt End_Dt ConfName $ ConfLoc $ ; DATALINES; 04/16/07 04/19/07 SAS Global Forum Orlando FL 06/03/07 06/06/07 PharmaSUG Denver CO 09/16/07 09/18/07 PNWSUG Seattle WA 09/30/07 10/02/07 SCSUG Austin TX 10/17/07 10/19/07 WUSS San Francisco CA 10/28/07 10/30/07 MWSUG Des Moines IA 11/04/07 11/06/07 SESUG Hilton Head SC 11/11/07 11/14/07 NESUG Baltimore MD ; PROBLEM: Embedded Blanks in character strings. Interactions Between the DATA Step and External Files

  35. Interactions … External Files Defining the Appearance of the Data LIST Input (and Output)Example of non-working code DATA SAS_Conf07; INFORMAT Start_Dt End_Dt mmddyy8. ; LENGTH ConfName $ 16 ConfLoc $ 16. ; INFILE DATALINES ; INPUT Start_Dt End_Dt ConfName $ & ConfLoc $ & ; DATALINES; 04/16/07 04/19/07 SAS Global Forum Orlando FL 06/03/07 06/06/07 PharmaSUG Denver CO 09/16/07 09/18/07 PNWSUG Seattle WA 09/30/07 10/02/07 SCSUG Austin TX 10/17/07 10/19/07 WUSS San Francisco CA 10/28/07 10/30/07 MWSUG Des Moines IA 11/04/07 11/06/07 SESUG Hilton Head SC 11/11/07 11/14/07 NESUG Baltimore MD ; SOLUTION: Use “&” format modifiers. Interactions Between the DATA Step and External Files

  36. Interactions … External Files Defining the Appearance of the Data LIST Input (and Output)QUICK ASIDE – Format Modifiers Interactions Between the DATA Step and External Files

  37. Interactions … External Files Defining the Appearance of the Data LIST Input (and Output)Example of non-working code DATA SAS_Conf07; INFORMAT Start_Dt End_Dt mmddyy8. ; LENGTH ConfName $ 16 ConfLoc $ 16. ; INFILE DATALINES ; INPUT Start_Dt End_Dt ConfName $ & ConfLoc $ & ; DATALINES; 04/16/07 04/19/07 SAS Global Forum Orlando FL 06/03/07 06/06/07 PharmaSUG Denver CO 09/16/07 09/18/07 PNWSUG Seattle WA 09/30/07 10/02/07 SCSUG Austin TX 10/17/07 10/19/07 WUSS San Francisco CA 10/28/07 10/30/07 MWSUG Des Moines IA 11/04/07 11/06/07 SESUG Hilton Head SC 11/11/07 11/14/07 NESUG Baltimore MD ; SOLUTION: Use “&” format modifiers. SOLUTION: Move CONFLOC variable 1 position to the right. Interactions Between the DATA Step and External Files

  38. Interactions … External Files Defining the Appearance of the Data LIST Input (and Output)Example of non-working code DATA SAS_Conf07; INFORMAT Start_Dt End_Dt mmddyy8. ; LENGTH ConfName $ 16 ConfLoc $ 16. ; INFILE DATALINES ; INPUT Start_Dt End_Dt ConfName $ & ConfLoc $ & ; ConfName = TRANSLATE( ConfName, ' ', '-' ); ConfLoc = TRANSLATE( ConfLoc, ' ', '-' ); DATALINES; 04/16/07 04/19/07 SAS Global Forum Orlando-------FL 06/03/07 06/06/07 PharmaSUG Denver--------CO 09/16/07 09/18/07 PNWSUG Seattle-------WA 09/30/07 10/02/07 SCSUG Austin--------TX 10/17/07 10/19/07 WUSS San Francisco-CA 10/28/07 10/30/07 MWSUG Des Moines----IA 11/04/07 11/06/07 SESUG Hilton Head---SC SOLUTION: Use “&” format modifiers. SOLUTION: Move CONFLOC variable 1 position to the right. SOLUTION: Insert dashes to separate city from state, change them back to blanks. Interactions Between the DATA Step and External Files

  39. Interactions … External Files Defining the Appearance of the Data LIST Input (and Output)Example of non-working code DATA SAS_Conf07; INFORMAT Start_Dt End_Dt mmddyy8. ; LENGTH ConfName $ 16 ConfLoc $ 16. ; INFILE DATALINES ; INPUT Start_Dt End_Dt ConfName $ & ConfLoc $ & ; ConfName = TRANSLATE( ConfName, ' ', '-' ); ConfLoc = TRANSLATE( ConfLoc, ' ', '-' ); DATALINES; 04/16/07 04/19/07 SAS Global Forum Orlando-------FL 06/03/07 06/06/07 PharmaSUG Denver--------CO 09/16/07 09/18/07 PNWSUG Seattle-------WA 09/30/07 10/02/07 SCSUG Austin--------TX 10/17/07 10/19/07 WUSS San Francisco-CA 10/28/07 10/30/07 MWSUG Des Moines----IA 11/04/07 11/06/07 SESUG Hilton Head---SC SOLUTION: Use “&” format modifiers. WAIT A MINUTE!!!Am I really suggesting that we change the data to accommodate the weaknesses of the routine? SOLUTION: Move CONFLOC variable 1 position to the right. SOLUTION: Insert dashes to separate city from state, change them back to blanks. Interactions Between the DATA Step and External Files

  40. Interactions … External Files Defining the Appearance of the Data Formatted Input (and Output) • Can specify informat / format for variables.(What does each variable look like?) • Can specify starting position in record.(Where is each variable on each record?) Interactions Between the DATA Step and External Files

  41. Interactions … External Files Defining the Appearance of the Data Formatted Input (and Output)Informats and Formats Handles length andappearance of data Interactions Between the DATA Step and External Files

  42. Interactions … External Files Defining the Appearance of the Data Formatted Input (and Output)Informats and Formats 5 types of Informat / Format: Not covered inthis presentation. • Character • Date / Time Not covered inthis presentation.PROC FORMAT • Numeric • Column Binary • User-Defined Interactions Between the DATA Step and External Files

  43. Interactions … External Files Defining the Appearance of the Data Formatted Input (and Output)Informats and Formats Formats can be assigned with: • INFORMAT statement (or FORMAT statement) • ATTRIB statement, using INFORMAT= FORMAT= • Specifying on INPUT / PUT Interactions Between the DATA Step and External Files

  44. Interactions … External Files Defining the Appearance of the Data Formatted Input (and Output)Column Pointers Two types of Column Pointers • Relative Column Pointers + • Absolute Column Pointers @ Interactions Between the DATA Step and External Files

  45. Interactions … External Files Defining the Appearance of the Data Formatted Input (and Output)Column Pointers File: 'c:\sasconf\sascon07.dat' 04/16/07 04/19/07 SAS Global Forum Orlando FL06/03/07 06/06/07 PharmaSUG Denver CO09/16/07 09/18/07 PNWSUG Seattle WA09/30/07 10/02/07 SCSUG Austin TX10/17/07 10/19/07 WUSS San Francisco CA10/28/07 10/30/07 MWSUG Des Moines IA11/04/07 11/06/07 SESUG Hilton Head SC11/11/07 11/14/07 NESUG Baltimore MD Interactions Between the DATA Step and External Files

  46. Interactions … External Files Defining the Appearance of the Data Formatted Input (and Output)Column Pointers – Relative DATA SAS_Conf07; RETAIN pt_loc 18 ; INFILE 'c:\sasconf\sascon07.dat'; INPUT Start_Dt mmddyy8. +1 End_Dt mmddyy8. +1 ConfName $char16. +1 ConfLoc $char16. ; FILE 'c:\sasconf\sascon07a.dat'; PUT Start_Dt date7. +10 ConfName $char16. +(-25) End_Dt date7. +(pt_loc) ConfLoc $char16. ;RUN; Basic example on INPUT statement. More advanced example on PUT statement. Interactions Between the DATA Step and External Files

  47. Interactions … External Files Defining the Appearance of the Data Formatted Input (and Output)Column Pointers – Absolute DATA SAS_Conf07; RETAIN pt_loc 36 ; INFILE 'c:\sasconf\sascon07.dat'; INPUT @ 1 Start_Dt mmddyy8. @ 10 End_Dt mmddyy8. @ 19 ConfName $char16. @ 36 ConfLoc $char16. ; FILE 'c:\sasconf\sascon07b.dat'; PUT @ 1 Start_Dt mmddyy8. @ 19 ConfName $char16. @ 10 End_Dt mmddyy8. @ pt_loc ConfLoc $char16.;RUN; Basic example on INPUT statement. More advanced example on PUT statement. Interactions Between the DATA Step and External Files

  48. Interactions … External Files Defining the Appearance of the Data Formatted Input (and Output)Column Pointers – Absolute – Character DATA SAS_Conf07; RETAIN pt_loc "LOC:" ; INFILE DATALINES ; INPUT @ 1 Start_Dt mmddyy8. @ '07 ' ConfName $char16. @ 10 End_Dt mmddyy8. @ pt_loc ConfLoc $char16. ;DATALINES;04/16/07 04/19/07 SAS Global Forum LOC:Orlando FL06/03/07 06/06/07 PharmaSUG LOC:Denver CO09/16/07 09/18/07 PNWSUG LOC:Seattle WA09/30/07 10/02/07 SCSUG LOC:Austin TX11/04/07 11/06/07 SESUG LOC:Hilton Head SC; Even more advanced example Interactions Between the DATA Step and External Files

  49. Interactions … External Files Defining the Appearance of the Data Formatted Input (and Output)Column Pointers – Absolute – Character Only use a pointer with a character string on INPUT. Try it with PUT, and you get: ERROR: The @'CHARACTER_STRING' or @CHARACTER_VARIABLE specification is valid on the PUT statement only in conjunction with the FILE ODS option. The execution of the DATA STEP is being terminated. Interactions Between the DATA Step and External Files

  50. Interactions … External Files Defining the Appearance of the Data Formatted Input (and Output)Line Pointers Two types of Column Pointers • Relative Column Pointers / • Absolute Column Pointers # Interactions Between the DATA Step and External Files

More Related