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

Hi,

I have a mainframe dataset that is generally formatted as below.  Each unique ‘ID’ has multiple consecutive rows associated with them.

 

Row 1                    ID                         Element1

Row 2                    Element 2            Element 3

Row 3                    Element 4            Element 5           

Row 4                    Element 6            Element 7            Element 8            Element 9            Element 10

 

Unfortunately, not all Unique IDs have 4 rows.  About half of the Unique IDs have only 3 rows associated with them.  This prevents me from using multiple input statements as I’ve tried here:

 

DATA TEMP(drop=TYPE);                    

  INFILE IN1;                            

  INPUT                                  

  TYPE $1. @;                            

  IF TYPE = 'A' THEN          /* each ID begins with ‘A’  */           

    DO;                                  

      INPUT                              

        @23 ID  $16.                 @66  Element1    $13.;

      INPUT                              

        @31 Element2  $8.      @45  Element3    $10;               

      INPUT                               

        @25 Element4  $14.   @65  Element5    $12;                 

      INPUT                               

        @1 Element6  $12.  @25  Element7   $4  @32  Element8   $4  @50  Element9    $4  @63  Element10    $4;                 

    END;                                 

 

Ideally, I would like help with a data step before executing the above that would insert a blank 4th row as needed for things to work.

 

Any immediate help is very much appreciated.

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

In that case, you are working with the proper tools already, and you are very close to a solution.  This should work:

 

DATA TEMP(drop=TYPE);                    

  INFILE IN1 end=done;             

      INPUT                              

        @23 ID  $16.                 @66  Element1    $13.;

      INPUT                              

        @31 Element2  $8.      @45  Element3    $10.;               

      INPUT                               

        @25 Element4  $14.   @65  Element5    $12.;  

 

if not done then do;

   input @23 Type $1. @@;

   if type ne 'A' then input 

        @1 Element6  $12.  @25  Element7   $4.  @32  Element8   $4.  @50  Element9    $4.  @63  Element10    $4.;

end;

run;

 

Notice a couple of features.  The double trailing @@ will hold the line of data for a subsequent INPUT statement, even when reaching the end of the programming statements.  And dots have been inserted in the INPUT statements where they were missing and required.

 

Good luck.

View solution in original post

7 REPLIES 7
Reeza
Super User

Can you post some more sample data so we can see the different types of records?

 

How do you know which row contains an ID versus a record with elements?

FollinLane
Obsidian | Level 7

Hi... the example was simplified but here are snapshots of actual data from same file.  Thank you.

 

Showing 4 rows per Account Number (Unique ID).

4rows.png

 

Showing 3 rows per Account Number (Unique ID).

3rows.png

Ksharp
Super User
Can you post your data as TEXT form (better is SAS code) ,not picture . which could allow us test it. And the output you want to see. And from the picture you posted, It seems the fourth row always starts with a digit number, I will judge it by:
........
input x ?? comma32. :
if not missing(x) then delete ;
.........

Astounding
PROC Star

If an ID has 3 rows of data instead of 4, is the missing line always the one that contains ELEMENT6 through ELEMENT10?

FollinLane
Obsidian | Level 7

Hi... yes the missing line is always the one that contains ELEMENT6 through ELEMENT10.  Thanks.

Astounding
PROC Star

In that case, you are working with the proper tools already, and you are very close to a solution.  This should work:

 

DATA TEMP(drop=TYPE);                    

  INFILE IN1 end=done;             

      INPUT                              

        @23 ID  $16.                 @66  Element1    $13.;

      INPUT                              

        @31 Element2  $8.      @45  Element3    $10.;               

      INPUT                               

        @25 Element4  $14.   @65  Element5    $12.;  

 

if not done then do;

   input @23 Type $1. @@;

   if type ne 'A' then input 

        @1 Element6  $12.  @25  Element7   $4.  @32  Element8   $4.  @50  Element9    $4.  @63  Element10    $4.;

end;

run;

 

Notice a couple of features.  The double trailing @@ will hold the line of data for a subsequent INPUT statement, even when reaching the end of the programming statements.  And dots have been inserted in the INPUT statements where they were missing and required.

 

Good luck.

FollinLane
Obsidian | Level 7

Astounding,

 

Thanks for your help.  It appears to be working well.  A good thing to know.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 7 replies
  • 1188 views
  • 0 likes
  • 4 in conversation