topic Re: .csv file issue in SAS Programming

.csv file issue

mauri0623 — Thu, 03 Oct 2019 15:05:41 GMT

Good morning,

I have the attached .csv file that when I use proc import the column length are not coming up the way I want them. I need Agency Location Code (ALC) to be of a character of length 10, Order Tracking Number (ODN) to be a character of length 17, Agency TAS to be a character of length 27 and Line of Accounting (LOA) to be a character of length 25. I do not to use the proc import.

Thank you!

-mauri

Re: .csv file issue

Tom — Thu, 03 Oct 2019 15:19:02 GMT

Why are you using PROC IMPORT to read a text file?

Just write a data step to read the text file. If you have no idea how to start use the code that PROC IMPORT generates (but know that it is much longer, confusing and uglier than code you would create yourself.)

Why not start by looking at the file?

366   options generic;
367   data _null_;
368     infile csv obs=3;
369     input;
370     list;
371   run;

NOTE: The infile CSV is:
      (system-specific pathname),
      (system-specific file attributes)

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+--
1         Agency Location Code (ALC),Order Tracking Number (ODN),Agency TAS,Line of Accounting (LOA) 90
2         00008522,DCAAHAA23H20PSI,, 26
3         00006551,,097 X4930005,9797XXXX4930005 20202020 49305L2P NOH H5IN ZHSG 2539D02 20GA00000000 DFAS
      98  RNJ00 16000000000000 033186 NOHH520MI101 137
NOTE: 3 records were read from the infile (system-specific pathname).
      The minimum record length was 26.
      The maximum record length was 137.

In general writing code to read a CSV is easier than running PROC IMPORT and dealing with the mess it creates.

Especially when the file only has FOUR variables!

data want;
  infile csv dsd truncover firstobs=2;
  input ALC :$10. ODN :$17. TAS :$27. LOA :$25.;
  label ALC='Agency Location Code (ALC)'
        ODN='Order Tracking Number (ODN)'
        TAS='Agency TAS'
        LOA='Line of Accounting (LOA)'
  ;
run;

Re: .csv file issue

Reeza — Thu, 03 Oct 2019 15:20:13 GMT

Look in the log after the import. Take the code form there and fix it as needed.

Yes, it's overkill and has too much extra stuff but it's the easiest way when you're just getting started.

@mauri0623 wrote:

Good morning,

I have the attached .csv file that when I use proc import the column length are not coming up the way I want them. I need Agency Location Code (ALC) to be of a character of length 10, Order Tracking Number (ODN) to be a character of length 17, Agency TAS to be a character of length 27 and Line of Accounting (LOA) to be a character of length 25. I do not to use the proc import.

Thank you!

-mauri

Re: .csv file issue

Tom — Thu, 03 Oct 2019 15:27:03 GMT

So you want to truncate the first column to only 10 characters and the last column to only 25 characters?

data test;
 infile csv dsd truncover firstobs=2;
 row+1;
 do col=1 to 4 ;
   input value :$200. @;
   length=lengthn(value);
   output;
 end;
run;
proc means min max n mean ;
  class col;
  var length;
run;

                        Analysis Variable : length

                  N
         col    Obs         Minimum         Maximum      N            Mean

           1     98       6.0000000      15.0000000     98       8.1530612

           2     98               0      20.0000000     98      12.0204082

           3     98               0      19.0000000     98      13.0306122

           4     98               0     114.0000000     98      43.9693878