sas - Extract the second occurrence of the string in SAS

Question

I have the following text with the currency and amount fields.

The transaction currency is USD and the amount is 200.00 The transaction currency is EUR and the amount is 150.00

I need to create four variables tran1, amt1, tran2 and amt2 variables

Tran1 amt1 Tran2 amt2
USD    200  EUR   150

I used substr and find functions but it is taking the first occurrence only.

 Tran1=substr(string,find(string,”transacrioncurrency”)+21,3);

 Amt1=substr(string,find(string,”the  amount is”)+12,5);

标签： sasfindsubstr

Answer 1

To find the second occurrence start after the position of the fisrt one :

data f;
p="The transaction currency is USD and the amount is 200.00 The transaction currency is EUR and the amount is 150.00";
p1=find(p,"The transaction currency",1);
Tran1=substr(p,p1+28,3);
p2=find(p,"the amount is",1);
 Amt1=substr(p,p2+14,6);

 p3=find(p,"The transaction currency",p1+1);
Tran2=substr(p,p3+28,3);
p4=find(p,"the amount is",p2+1);
 Amt2=substr(p,p4+14,6);
;

run;

result :

p                 p1    Tran1   p2  Amt1    p3  Tran2   p4  Amt2
The transacti..     1   USD     37  200.00  58  EUR     94  150.00