Convert text to structured data

[hellind]

Haven't used SAS for more than a year and is getting rusty.

I would like to convert INPUT to OUTPUT as below

Input:

Scheme ID: 3111
Product:
MUREX INR/USD FX Futures (IU)

Incentive:
The Participant shall receive a cash incentive of US$0.50 per lot traded electronically. The total cash incentive payment shall be capped at US$10,000 per month.


Scheme ID: 3115
Product:
MUREX INR/USD FX Futures (IU)

Incentive:
Cash incentive of US$0.50 per lot of IU traded, subject to the Participant fulfilling the market making obligations and conditions specified below. The total cash incentive payment shall be capped at US$5,000 per month.

Conditions:
Beautiful pebbles of varied sizes, suitable to decorate fish tank, small fountain, patio decoration:

i) Spot Month outright
Comes in different colours, white, and black. Very seldom used
ii) Your safety is important to us and we believe that you can stay safe.
Self collect near buangkok MRT station.

Output:

SchemeID	Product	Incentive	Conditions
3111	MUREX INR/USD FX Futures	The Participant shall receive a cash incentive of US$0.50 per lot traded electronically. The total cash incentive payment shall be capped at US$40,000 per month.
3115	MUREX INR/USD FX Futures (IU)	Cash incentive of US$0.50 per lot of IU traded, subject to the Participant fulfilling the market making obligations and conditions specified below. The total cash incentive payment shall be capped at US$5,000 per month.	Beautiful pebbles of varied sizes, suitable to decorate fish tank, small fountain, patio decoration: i) Spot Month outright Comes in different colours, white, and black. Very seldom used ii) Your safety is important to us and we believe that you can stay safe. Self collect near buangkok MRT station.

[hellind]

I have loaded the free text into a dataset.

Basically the logic to be applied is:
1. If "Scheme ID:" exists, create a new row in dataset
2. schemeid = scan(c,2,":")
3. Product is in next line
4. Incentive is next line and can be multiple lines until S

[Reeza]

Not a straight forward file but looks like you have keywords to use. 

Heres an untested skeleton of one way to structure your code:

Make sure to assign lengths that are long enough for all variables. 

Retain schemeId product incentive condition;

Input record;

if record =: 'Scheme ID' then do; 

output; 

call missing(schemeID, product, incentive, condition);

SchemeID = scan(record, .... ); 

end; 

Else if record=: 'product' then do;

input text2;

product=text2;

end;

ele if record=:'incentive' then do; 

...

end; 

[BrunoMueller]

Find below some sample code that gets you close to what you want. You have to decide how you want to handle the empty lines and which character(s) to but between the text that goes over several lines.

filename mydata temp;
data _null_;
  infile cards truncover;
  input line $256.;
  putlog line=;
  file mydata;
  put line;
cards4;
Scheme ID: 3111
Product:
MUREX INR/USD FX Futures (IU)

Incentive:
The Participant shall receive a cash incentive of US$0.50 per lot traded electronically. The total cash incentive payment shall be capped at US$10,000 per month.


Scheme ID: 3115
Product:
MUREX INR/USD FX Futures (IU)

Incentive:
Cash incentive of US$0.50 per lot of IU traded, subject to the Participant fulfilling the market making obligations and conditions specified below. The total cash incentive payment shall be capped at US$5,000 per month.

Conditions:
Beautiful pebbles of varied sizes, suitable to decorate fish tank, small fountain, patio decoration:

i) Spot Month outright
Comes in different colours, white, and black. Very seldom used
ii) Your safety is important to us and we believe that you can stay safe.
Self collect near buangkok MRT station.
;;;;





data want;
  infile myData truncover end=last;
  length
    schemeID $ 16
    product $ 40
    incentive $ 1024
    conditions $ 1024
  ;
  retain
    schemeID
    product
    incentive
    conditions
    flag_incentive
    flag_conditions
  ;
  input
    line $256. 
  ;
  if line =: "Scheme ID:" then do;    
    if _n_ > 1 then do;
      output;
      call missing(product, incentive, conditions);
      call missing(flag_incentive, flag_conditions);
    end;
    schemeID = scan(line, -1, " ");
  end;

  if line =: "Product:" then do;  
    input product $64.;
  end;
  if line =: "Incentive:" then do;    
    call missing(flag_incentive, flag_conditions);
    flag_incentive = 1;    
    return;
  end;

  if line =: "Conditions:" then do;    
    call missing(flag_incentive, flag_conditions);
    flag_conditions = 1;
    return;
  end;

  if flag_incentive = 1 then do;
    incentive = catx("<BR>", incentive, line);
  end;

  if flag_conditions = 1 then do;
    conditions = catx("<BR>", conditions, line);
  end;

  if last = 1 then do;
    output;
  end;
  drop line;
run;
filename mydata clear;

proc print data=want;
run;

Bruno

[Ksharp]

data temp;
  infile cards truncover ;
  input line $256.;
  length schemeid vname value $ 300;
  retain schemeid vname;
  if line =: 'Scheme ID' then do;schemeid=scan(line,-1,':');delete;end;
   else if prxmatch('/^[\w\s]+\:$/',strip(line)) then do;vname=scan(line,1,':'); delete;end;
    else if not missing(line) then do;value=line;output;end;
  drop line;
cards4;
Scheme ID: 3111
Product:
MUREX INR/USD FX Futures (IU)

Incentive:
The Participant shall receive a cash incentive of US$0.50 per lot traded electronically. The total cash incentive payment shall be capped at US$10,000 per month.


Scheme ID: 3115
Product:
MUREX INR/USD FX Futures (IU)

Incentive:
Cash incentive of US$0.50 per lot of IU traded, subject to the Participant fulfilling the market making obligations and conditions specified below. The total cash incentive payment shall be capped at US$5,000 per month.

Conditions:
Beautiful pebbles of varied sizes, suitable to decorate fish tank, small fountain, patio decoration:

i) Spot Month outright
Comes in different colours, white, and black. Very seldom used
ii) Your safety is important to us and we believe that you can stay safe.
Self collect near buangkok MRT station.
;;;;
run;
data temp1;
length v $ 32767;
 do until(last.vname);
  set temp;
  by schemeid vname notsorted;
  v=catx(' ',v,value);
 end;
 drop value;
run;
proc transpose data=temp1 out=want(drop=_name_);
 by schemeid notsorted;
 id vname;
 var v;
run;

[mkeintz]

Here' a single step solution that uses the often overlooked INPUT @"character string" technique.  And because the routine looks ahead for the next schemeid, there is an explicity output statement, followed by an update to schemeid (which is retained).

It makes the following assumptions.

1. the order of elements are SCHEMEID, PRODUCT, INCENTIVES, and (optionally) CONDITIONS.
2. schemeid is a single line
3. there can be no lines or any number of blank lines between schemeid and productid
4. product id is two lines, one with the keyword and one with the value
5. there can be no lines or any number of blank lines between productid and incentives
6. incentives can have multiple lines and are always terminated with a blank line (although that could be easily relaxed).
7. conditions can have any number of lines and are terminated wiht a new schemeid line.

With the exception of schemeid, it would be relatively straightforward to accomodate any order of the elements. 

data want (drop=_:);
  infile 'c:\temp\t.txt' end=eod;
  retain schemeid .;
  if schemeid=. then input @ "Scheme ID:"  schemeid;
  input @ "Product:" / product && $80.;
  length incentive $500;
  input @ "Incentive:" / ;
  do until (_infile_=' ');
    incentive=catx(' ',incentive,_infile_);
    input;
  end;
  length conditions $1000;
  do until (eod or index(_infile_,"Scheme ID:"));
    input;
    if _condition_found then conditions=catx(' ',conditions,_infile_);
    else if index(_infile_,"Conditions:") then _condition_found=1;
  end;
  output;
  if eod=0 then schemeid=input(scan(_infile_,3),10.);
run;