Problem importing text file

[svh]

I have a text file that is space-delimited and I cannot import the data into a table. It seems that I'm missing a command in the infile statement or in the input statement, but have had no luck finding it. The warning messages tell me that the variable Metric was read in correctly, but there is a missing value for the next variable in the list (adjusted_cohort). Can someone send a suggestion or resource about importing text files that resemble list input? File is attached for reference.

data want;
infile '<path>\om_2008.txt'  truncover ;
input Metric : $22. adjusted_cohort  adjusted_cohort_pct completers completers_pct enrolled enrolled_pct enrolled_other enrolled_other_pct enroll_unknown enroll_unknown_pct;
run;

[ballardw · Accepted solution]

---

@svh wrote:

I have a text file that is space-delimited and I cannot import the data into a table. It seems that I'm missing a command in the infile statement or in the input statement, but have had no luck finding it. The warning messages tell me that the variable Metric was read in correctly, but there is a missing value for the next variable in the list (adjusted_cohort). Can someone send a suggestion or resource about importing text files that resemble list input? File is attached for reference.

data want;
infile '<path>\om_2008.txt'  truncover ;
input Metric : $22. adjusted_cohort  adjusted_cohort_pct completers completers_pct enrolled enrolled_pct enrolled_other enrolled_other_pct enroll_unknown enroll_unknown_pct;
run;

---

If you file is as the attachment the second variable appears to have commas in the values such as 50,672. If you do not tell SAS with an appropriate informat that the values should contain commas then it will be missing. Try

data want;
infile '<path>\om_2008.txt'  truncover ;
input Metric : $22. adjusted_cohort :comma10. 
   adjusted_cohort_pct completers completers_pct enrolled
   enrolled_pct enrolled_other enrolled_other_pct enroll_unknown
   enroll_unknown_pct;
run;

[pink_poodle]

I am going to give you the code from the import task that I ran in SAS Enterprise Guide, which imported your file successfully. It uses MISOVER and DSD options. You can also change variable names from F1 - F12 to the right names.

/* --------------------------------------------------------------------
   Code generated by a SAS task
   
   Generated on Wednesday, September 26, 2018 at 4:44:29 PM
   By task:     Import Data Wizard
   
   Source file: C:\Users\User\Downloads\OM_2008.txt
   Server:      Local File System
   
   Output data: WORK.OM_2008
   Server:      Local
   -------------------------------------------------------------------- */

/* --------------------------------------------------------------------
   This DATA step reads the data values from DATALINES within the SAS
   code. The values within the DATALINES were extracted from the text
   source file by the Import Data wizard.
   -------------------------------------------------------------------- */

DATA WORK.OM_2008;
    LENGTH
        F1               $ 22
        F2                 8
        F3                 8
        F4                 8
        F5                 8
        F6                 8
        F7                 8
        F8                 8
        F9                 8
        F10                8
        F11                8
        F12                8 ;
    FORMAT
        F1               $CHAR22.
        F2               BEST9.
        F3               BEST7.
        F4               BEST9.
        F5               BEST7.
        F6               BEST6.
        F7               BEST4.
        F8               BEST7.
        F9               BEST5.
        F10              BEST7.
        F11              BEST6.
        F12              BEST4. ;
    INFORMAT
        F1               $CHAR22.
        F2               BEST9.
        F3               BEST7.
        F4               BEST9.
        F5               BEST7.
        F6               BEST6.
        F7               BEST4.
        F8               BEST7.
        F9               BEST5.
        F10              BEST7.
        F11              BEST6.
        F12              BEST4. ;
    INFILE DATALINES4
        DLM='7F'x
        MISSOVER
        DSD ;
    INPUT
        F1               : $CHAR22.
        F2               : ?? COMMA9.
        F3               : ?? COMMA7.
        F4               : ?? COMMA9.
        F5               : ?? COMMA7.
        F6               : ?? COMMA6.
        F7               : ?? COMMA4.
        F8               : ?? COMMA7.
        F9               : ?? COMMA5.
        F10              : ?? COMMA7.
        F11              : ?? COMMA6.
        F12              : ?? COMMA4. ;
DATALINES4;
FourYearPrivateNPPTNFT50,672100.022,53344.59401.910,24120.216,95833.5
FourYearAll2,985,291100.01,599,29653.633,9031.1537,53518.0814,55727.3
FourYearPublic1,724,244100.0973,35856.523,1651.3370,16221.5357,55920.7
FourYearPublicFTFT1,061,294100.0620,79658.513,9321.3233,13822.0193,42818.2
FourYearPublicPTFT108,489100.023,63721.83,0822.831,31028.950,46046.5
FourYearPublicFTNFT392,736100.0260,87366.43,2800.863,47016.265,11316.6
FourYearPublicPTNFT161,725100.068,05242.12,8711.842,24426.148,55830.0
FourYearPrivateNP714,794100.0449,07962.83,5420.5131,20818.4130,96518.3
FourYearPrivateNPFTFT500,917100.0327,27665.31,3070.395,62119.176,71315.3
FourYearPrivateNPPTFT17,555100.05,22129.73992.33,21318.38,72249.7
FourYearPrivateNPFTNFT145,650100.094,04964.68960.622,13315.228,57219.6
FourYearPrivateFP546,253100.0176,85932.47,1961.336,1656.6326,03359.7
FourYearPrivateFPFTFT201,213100.056,78928.27900.47,7953.9135,83967.5
FourYearPrivateFPPTFT54,855100.07,62113.91,8753.46,59412.038,76570.7
FourYearPrivateFPFTNFT196,564100.086,36943.91,2410.611,4705.897,48449.6
FourYearPrivateFPPTNFT93,621100.026,08027.93,2903.510,30611.053,94557.6
TwoYearAll1,780,384100.0563,13131.633,4901.9521,19929.3662,56437.2
TwoYearPublic1,544,399100.0410,56826.631,4822.0510,70933.1591,64038.3
TwoYearPublicFTFT637,701100.0209,45332.813,5542.1185,47029.1229,22435.9
TwoYearPublicPTFT395,800100.067,10317.010,4672.6125,56831.7192,66248.7
TwoYearPublicFTNFT198,954100.070,78435.62,6151.367,40233.958,15329.2
TwoYearPublicPTNFT311,944100.063,22820.34,8461.6132,26942.4111,60135.8
TwoYearPrivateNP31,775100.019,78162.3580.22,4857.89,45129.7
TwoYearPrivateNPFTFT26,296100.016,86664.1360.11,8927.27,50228.5
TwoYearPrivateNPPTFT1,681100.056433.6120.71307.797558.0
TwoYearPrivateNPFTNFT2,470100.01,56663.440.231412.758623.7
TwoYearPrivateNPPTNFT1,328100.078559.160.514911.238829.2
TwoYearPrivateFP204,210100.0132,78265.01,9501.08,0053.961,47330.1
TwoYearPrivateFPFTFT 152,049100.0100,35666.06560.45,1873.445,85030.2
TwoYearPrivateFPPTFT9,084100.03,57539.492110.13553.94,23346.6
TwoYearPrivateFPFTNFT39,846100.027,26968.42820.72,3055.89,99025.1
TwoYearPrivateFPPTNFT3,231100.01,58249.0912.81584.91,40043.3
;;;;

Modified List Input: Colon Modifier information:

The : (colon) format modifier enables you to use list input but also to specify an informat after a variable name, whether character or numeric. SAS reads until it encounters a blank column, the defined length of the variable (character only), or the end of the data line, whichever comes first.