Strip missing in columns

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

Strip missing in columns

Hi guys,

I have the following code to separate multiple columns from one single column:

Example1:

DATA F502_ARRANGE;

    SET WORK.f502_teste;

  IF STRIP(F1) =: "1"         THEN TP_EVENTO       = STRIP(F1);

  IF STRIP(INDEX(F1,"&ANO.")) THEN DT_TRANSACAO   = STRIP(F1);

  IF STRIP(INDEX(F3,'000'  )) THEN CD_TRANSACAO       = STRIP(F3);

RUN;

But my txt file has records like the follows:

------------------------------------------------------------------------------------------------------------------------------------         

Var1Var2
1 0000012
31/07/201521:44:36
10000012
31/07/201521:44:43  

When i run the code i get this result:

10000012
31/07/2015
10000012
31/07/2015
10000012

I need to remove these blanks but without loosing the other values, i was thinking about to duplicate the values, but these files are large and will take twice the time i take.

So how can i do that, but faster ?


Thnx,


Rodrigo Dartibali Elias


Accepted Solutions
Solution
‎08-10-2015 11:21 AM
Super User
Posts: 19,167

Re: Strip missing in columns


All Replies
Super User
Posts: 19,167

Re: Strip missing in columns

It looks like your data crosses lines so you should have something like:

1 0000012 31/07/2015 21:44:36

as a single record?

Can you post a sample of your actual txt file? I'm sure there are ways to read that in directly correctly.

Regular Contributor
Posts: 212

Re: Strip missing in columns

Ok

Regular Contributor
Posts: 212

Re: Strip missing in columns

     1              0000012            CAN NOT SHOW                                   SECRETDATA

31/07/2015         21:45:52        00081124252053      00000000000039342778      000000000000029,00

     1              0000012            CAN NOT SHOW                                   SECRETDATA

31/07/2015         21:45:53        00000086955713      00000000000052632997      000000000000082,40

     1              0000012            CAN NOT SHOW                                   SECRETDATA

31/07/2015         21:45:53        00049008919672      00000000000039728005      000000000000007,50

     1              0000012            CAN NOT SHOW                                   SECRETDATA

31/07/2015         21:45:55        00034565037840      00000000000029896630      000000000000090,00

     1              0000012            CAN NOT SHOW                                   SECRETDATA

31/07/2015         21:45:57        00035243586838      00000000000032563108      000000000000038,59

     1              0000012            CAN NOT SHOW                                   SECRETDATA

31/07/2015         21:45:59        00028607340824      00000000000003618374      000000000000014,50

     1              0000012            CAN NOT SHOW                                   SECRETDATA

31/07/2015         21:46:00        00096283548920      00000000000022258442      000000000000150,00

     1              0000012            CAN NOT SHOW                                   SECRETDATA

31/07/2015         21:46:01        00003389575022      00000000000038736420      000000000000007,54

     1              0000012            CAN NOT SHOW                                   SECRETDATA

31/07/2015         21:46:02        00010726570826      00000000000022695605      000000000000005,99

     1              0000012            CAN NOT SHOW                                   SECRETDATA

31/07/2015         21:46:10        00078281725591      00000000000037681575      000000000000038,60

     1              0000012            CAN NOT SHOW                                   SECRETDATA

31/07/2015         21:46:12        00072701625149      00000000000035865199      000000000000055,00

     1              0000012            CAN NOT SHOW                                   SECRETDATA

31/07/2015         21:46:13        00032898715840      00000000000024506400      000000000000025,00

     1              0000012            CAN NOT SHOW                                   SECRETDATA

31/07/2015         21:46:14        00038224165841      00000000000021382409      000000000000042,00

Regular Contributor
Posts: 212

Re: Strip missing in columns

The first and the second lines are in the same line actualy.

Regular Contributor
Posts: 212

Re: Strip missing in columns

Sorry, 'should' be at the same line, but they are not.

Super User
Posts: 19,167

Re: Strip missing in columns

Can you include it as an actual .txt file?

Regular Contributor
Posts: 212

Re: Strip missing in columns

I don't know to attach it. But its the same i have here.

Regular Contributor
Posts: 212

Re: Strip missing in columns

And there are records that are relevant to my enterprise.

Super User
Posts: 19,167

Re: Strip missing in columns

You can go to use Advanced Editor, top right hand corner of message box and attach file there.

Otherwise, here's several methods to read multiple lines using a data step:

1. Use multiple input statements

2.Use line control

http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a002001051.htm

Good Luck!

Regular Contributor
Posts: 212

Re: Strip missing in columns

I've found a way tricky but works very well.

DATA TMP_F502_ORIGINAL;

INFILE"PATH/FILE_*.txt" LRECL = 250 MISSOVER PAD;

INPUT @1 ENTIRE_ROW $250.;

RUN;

RETAINVAR1 "0" VAR2 "0000000"
VAR3 "000000000000000000000000000000000000000000000000000000000000"
VAR4 "0000000000"
VAR5 "01/01/1900"
VAR6  "00:00:00"
VAR7 "00000000000000"
VA8 "00000000000000000000";
IF SUBSTR(ENTIRE_ROW,1,1) = "1" THEN
DO;
VAR1      = STRIP(SUBSTR(ENTIRE_ROW,1,1));
VAR2          = STRIP(SUBSTR(ENTIRE_ROW,15,9));
VAR3 = STRIP(SUBSTR(ENTIRE_ROW,35,40));
VAR4   = STRIP(SUBSTR(ENTIRE_ROW,85,30));
END;
IF SUBSTR(ENTIRE_ROW,7,5) = "2015"  THEN
DO;
VAR5 = STRIP(SUBSTR(ENTIRE_ROW,1,14));
VAR6 = STRIP(SUBSTR(ENTIRE_ROW,20,14));
VAR7     = STRIP(SUBSTR(ENTIRE_ROW,35,25));
VAR8    = STRIP(SUBSTR(ENTIRE_ROW,54,35));
END;

It works fine

Thanks

Super User
Super User
Posts: 7,720

Re: Strip missing in columns

Hi,

So two things.  Firstly, and most important, is to make your code readable.  This means, lower case it all, use smaller consistent indentations.  I cannot read your code above.  Secondly, Reeza has already provided you with the correct approach to take.  You simply need to look at reading the file in correctly, not post-processing it.  Simple code looks like this:

data want;

  infile datalines;

  length var1 8 var2 $10 var3 8 var4 8;

  informat var3 ddmmyy10.

               var4 hhmmss10.;

  format var3 date9.

            var4 time8.;

  input #1 var1 var2 $

          #2 var3 var4;

datalines;

1 0000012

31/07/2015 21:44:36

1 0000012

31/07/2015 21:44:43

;

run;

Regular Contributor
Posts: 212

Re: Strip missing in columns

I read, His approach. I configured it as it says. But didn't work for my needs.

About formatting my code: the enterprise i am, use all theier sas programs like this(All in UPCASE). The code in the text box it is not estructured as in my program, anyway

Thank you all for the answers.

Solution
‎08-10-2015 11:21 AM
Super User
Posts: 19,167

Re: Strip missing in columns

Her approach.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 319 views
  • 2 likes
  • 3 in conversation