SAS can't read following space delimited file

Question

Problem Statement: I have a text file and I want to read it using SAS INFILE function. But SAS is not giving me the proper output.

Text File:

1 Big Bazar 15,000
2 Hypercity 20,000
3 Star Bazar 25,000
4 Big Basket 30,000
5 Grofers 35,000
6 DMart 40,000

The Code that I have tried:

DATA Profit;
INFILE '/folders/myfolders/Akki/Retain_Sum.txt';

INPUT Month $1 Name $3-12 Profit;
Informat Profit COMMA6.;
FORMAT Profit COMMA6.;

RETAIN Cummulative_Profit;
Cummulative_Profit = SUM(Cummulative_Profit, Profit);
Run;

PROC PRINT data=profit;
Run;

What am I looking for? I want to read above data in SAS but it seems there is a problem in my code. (Whenever I run my code it gives some missing value in the profit variable of Grofers and DMart observation). Can you fix it? I want SAS to read complete file. Thanks in advance.

Answer 1

Your problem comes from the fact that you are specifying column input for your second variable, saying it should read from column 3 to 12. While it works for the first 4 entries, the last two are two short and it reads in the beginning of the profit value in the name variable.

Since your file is clearly not "fixed width", you should be using list input. Unfortunately because your name values contain spaces, this could prove tricky. The proper way to do it would be to have your name values quoted in your text file. You can then use the dsd option on your infile statement to read these values properly with list input:

DATA Profit;
INFILE datalines dlm=' ' dsd;
length month $1 name $12;
INPUT Month $ Name $ Profit;
Informat Profit COMMA6.;
FORMAT Profit COMMA6.;

RETAIN Cummulative_Profit;
Cummulative_Profit = SUM(Cummulative_Profit, Profit);
datalines;
1 "Big Bazar" 15,000
2 "Hypercity" 20,000
3 "Star Bazaar" 25,000
4 "Big Basket" 30,000
5 Grofers 35,000
6 DMart 40,000
;
Run;

PROC PRINT data=profit;
Run;

Answer 2

Your file does not conform to the rules for LIST input with imbedded blanks. You can still read it without changing the file but you have to find the column where the name field ends.

filename FT15F001 temp;
data bad;
   infile FT15F001 col=col;
   input month @;
   l = findc(_infile_,' ','b') - col +1;
   input name $varying32. l profit :comma.;
   format profit comma12.;
   drop l;
   parmcards;
1 Big Bazar 15,000
2 Hypercity 20,000
3 Star Bazar 25,000
4 Big Basket 30,000
5 Grofers 35,000
6 DMart 40,000
;;;;
   run;
proc print;
   run;

 Obs    month    name                profit

  1       1      Big Bazar           15,000
  2       2      Hypercity           20,000
  3       3      Star Bazar          25,000
  4       4      Big Basket          30,000
  5       5      Grofers             35,000
  6       6      DMart               40,000