How to import Sales column into SAS with data step? Lengths of sales values vary.

[roquesagum]

Hi all,

Could somebody help me with this issue?

I am a SAS student working on an assignment which is a small project about customer distribution and deactivation. I am importing the raw data into SAS with data step. 

The last column is Sales. The length of sales values under this column vary from 5 to 7 in the format of $20.00, $105.12, and $1298.22. (Please see the attached file with a small fraction of the original data.) I want to import them into SAS in their original format including the dollar sign. After that, I will work on them for some analyses.

I tried different ways but all failed.  I think the issue is that I don't know how to deal with the different lengths of values under that column.

I am looking forward to your help.

Thank you very much. 

Sagum

[Tom · Accepted solution]

You don't "import" from a text file.  You just READ the text file.  There are no "different lengths" under that column.  SALES is always in columns 82 to 92 on every line for the file you shared.

Use the COMMA informat, just like you need to use the MMDDYY informat for the date fields.  You can use the @ cursor motion command to position the cursor in the right place to read those strings that require informats.

data want;
  infile 'c:\downloads\partial raw data.txt' truncover ;
  input id $ 1-13 @15 date1 mmddyy10. @26 date2 mmddyy10. /* other variables */ @82 sales comma11. ;
  format date1 date2 yymmdd10. sales dollar11.2;
run;

Results:

Obs         id               date1         date2          sales

  1    1176913194483    1999-06-20             .        $128.00
  2    1176914599423    1999-10-04    1999-10-15         $72.00
  3    1176951913656    2000-07-01             .        $593.00
  4    1176954000288    2000-05-30             .         $83.00
  5    1176969186303    2000-12-13             .            .
  6    1176991056273    1999-08-31    2000-09-18      $1,041.00
  7    1176991866552    2000-05-24             .            .
  8    1176992889500    2000-11-28             .         $72.00
  9    1177000067271    1999-12-23             .        $134.00
 10    1177010940613    1999-12-09             .         $11.00

[roquesagum]

Hi Tom,

Thank you so much for your prompt response. Your solution works.

There is a lot to learn for me.

Sagum