BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mauri0623
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

 

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

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;

 

 

Reeza
Super User

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


 

Tom
Super User Tom
Super User

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

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 560 views
  • 0 likes
  • 3 in conversation