BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
svh
Lapis Lazuli | Level 10 svh
Lapis Lazuli | Level 10

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;
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@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;

View solution in original post

2 REPLIES 2
ballardw
Super User

@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
Barite | Level 11

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.

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1134 views
  • 2 likes
  • 3 in conversation