Can we read multi layout File in SAS Enterprise Guide 8.3 ?

[THALLA_REDDY]

Hi Team,

I have a file need to be imported into SAS EG 8.3 using IMPORT TASK, My file is having 3 types of records in the file (Header, Detail & Trailer records). I have to import the data into 3 different datasets based on the REC_TYPE filed (Which is the first and common field in all 3 types of records), but the IMPORT TASK is not permitting this. Can anyone help me on this issue ??

thanks,

Thalla

[ChrisHemedinger]

The Import Data task won't support that, no.  You'll have to use DATA step, which is actually well-suited to the task.  But it's a learning exercise if you haven't done this before.

Here's an example (a bit long, but you can run it as-is) that shows how to read different fields based on a record-type indicator.  Your situation might be different, but maybe this will give you an idea of what's possible.

/* Download the ZIP file */
filename dl "%sysfunc(getoption(WORK))/ri130701_13dn01.zip";

proc http 
  url="http://www.freddiemac.com/mbs/data/stacr/ri130701_13dn01.zip"
  method='GET'
  out=dl
  ct="application/zip";
run;

filename dl clear;

/* Crack it open and read with FILENAME ZIP */
filename inzip ZIP "%sysfunc(getoption(WORK))/ri130701_13dn01.zip";

data orig;
  infile inzip(ri130701_13dn01.txt);
  input @1 record_type $2. @;

  if record_type = '00' then do;
    input  record_type $ 1 - 2 file_name $ 4 - 15 file_date 17 - 24;
    output;
    end;

  if record_type = '10' then do;
    input
      reference_pool_number $ 4 - 9
      reference_pool_issuance_upb 11 - 25
      reference_pool_factor 27 - 38
      cummulative_ce_factor 40 - 51
      total_def_on_ce 53 - 58
      total_def_on_ce_upb 60 - 74
      uw_def_on_ce 76 - 81
      uw_def_on_ce_upb 83 - 97
      uncfd_def_on_ce_resc 99 - 104
      uncfd_def_on_ce_resc_upb 106 - 120;
    output;
    end;

  if record_type = '20' then do;
    input
      loan_identifier $ 4 - 15
      product_type $ 17 - 21
      seller_name $ 23 - 52
      property_state $ 54 - 55
      postal_code $ 57 - 61
      msa 63 - 67
      first_payment_date 69 - 74
      maturity_date 76 - 81
      original_loan_term 83 - 85
      original_interest_rate 87 - 92
      original_upb 94 - 105
      upb_at_issuance 107 - 118
      loan_purpose $ 120 - 120
      channel $ 122 - 122
      property_type $ 124 - 125
      number_of_units 127 - 128
      occupancy $ 130 - 130
      number_of_borrowers 132 - 133
      first_time_homebuyer $ 135 - 135
      prepayment_penalty $ 137 - 137
      credit_score 139 - 141
      original_ltv 143 - 145
      original_cltv 147 - 149
      original_dti 151 - 153
      mi_cov_pct 155 - 157;
      output;
    end;

  if record_type = '50' then
    do;
      input
        servicer_name $ 17 - 46
        loan_age 48 - 50
        remaining_mos_to_maturity 52 - 54
        adj_remaining_mos_to_maturity 56 - 58
        current_delinquency_status 60 - 61
        payment_history 63 - 74
        current_interest_rate 76 - 81
        current_actual_upb 83 - 94
        current_interest_bearing_upb 96 - 107
        upb_at_pool_removal 109 - 120
        zero_balance_code 122 - 123
        zero_balance_effective_date 125 - 130
        uw_defect_settlement_date 132 - 137
        modification_flag $ 139 - 139
      ;
      output;
    end;

  retain _all_;
run;

filename inzip clear;

[THALLA_REDDY]

thanks for your reply,

Yes this helps me to understand my requirement. So, we won't get the above requirement fulfilled with Import TASK and we can go with DATA STEP right ?

thanks,

Thalla

[ChrisHemedinger]

Yes, that's correct.  Usually data of this type is accompanied by a data dictionary/record layout specification.  You can use that as a recipe for building your DATA step.  Sometimes (if you are very lucky) the data provider will furnish sample programs (in SAS and other tools) to read these.

[Kurt_Bremser · Accepted solution]

Yes, go with the data step. There are always moments where the wizards in supporting software come up short, and you have to dig into the code itself. Keep in mind that most of the time the Import Wizard in EG does nothing more than transfer data to the SAS server and run a data step there.

A file format like you describe is not uncommon. Over here in Austria we use something similar in communicating motor vehicle licensing data between the Austrian equivalent of the DMV and the insurers.

The data step is THE Swiss Army Knife of the SAS language. Basically you can say that any solution is doable in a data step, and that all the fancy procedures are "just" there to simplify certain tasks. So any time you spend learning more about it is time well spent.

[THALLA_REDDY]

sure, will spend some time to simply this kind of requirements. Thanks for your valuable response.