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. |
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;
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
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;
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.