Splitting Values of a String variable based on semicolon & add those record to the same table in SAS

[RaviSPR]

Hi,

I have a table as below. Here the order_details may contains more or less orders.

I need the below output table. Can anyone please help me how we can achieve it in SAS.

Input: 

order_id	order_detail
O1	;product1;3000;7020.00,;product2;2000;1340.00,product7;100;1220.00
O2	;product3;5;No Charge,;product4;25;No Charge
O3	;product3;5;500.00,;product4;15;No Charge

Our goal is to split this into multiple records as below: 

Output: 

Order_ID	Product	Units	Price
O1	product1	3000	7020.00
O1	product2	2000	1340.00
O1	product7	100	1220.00
O2	product3	5	0.00
O2	product4	25	0.00
O3	product3	5	500.00
O3	product4	15	0.00

Order_ID         Product        Units      Price
========= ========== ======= =======
O1              product1 3000 7020.00
O1              product2 2000 1340.00
O1              product7 100   1220.00
O2              product3  5       0.00
O2              product4  25     0.00
O3              product3  5      500.00
O3              product4 15      0.00

My code:

filename src "/xxxx/xxx/xxx/source.csv"; 

Data rawdata; 

infile src truncover dsd firstobs=1; 

/*Retain order_id Product Units Price;*/ 

input order_id  : $2.  

  order_detail  : $100. 

;  

Run; 

Data  p3 (Keep=order_id Product Units Price); 

Retain order_id Product Units Price; 

Set rawdata; 

Array P (10) $50; 

   do i=1 to countw(order_detail,',;'); 

      P[i]=scan(order_detail,i,',;'); 

  if i in (1, 4, 7) then do;  

   order_id = order_id; 

Product = P[i] ;  

/* output P1 ; */ 

end; 

  if i in (2, 5, 😎 then  do;  

Units = P[i] ; 

/* output P2;*/ 

end; 

  if i in (3, 6, 9) then  do;  

Price = P[i] ; 

output P3; 

end; 

   end; 

   drop i; 

Run; 

Data final; 

set p3; 

if price='No Charge' then price = '0.00'; 

run; 

But here in IF condition I dont want to use numbers like (1, 4, 7) because I may get more & more Order Details for the same OderID.

Can anyone suggest any better approach?

Thank You
RaviSPR

[yabwon · Accepted solution]

Hi, try like this:

data have;
  infile cards4 dlm = "|";
  input order_id :$ order_detail :$ 100.;
cards4;
O1|;product1;3000;7020.00,;product2;2000;1340.00,product7;100;1220.00
O2|;product3;5;No Charge,;product4;25;No Charge
O3|;product3;5;500.00,;product4;15;No Charge
;;;;
run;

data want;
  set have;

  _N_ = countw(order_detail,",");
  put _N_=;
  do _N_ = 1 to _N_;
   
    part = scan(order_detail, _N_, ",");
    drop part order_detail;
    Product = scan(part,1,";");
    Units   = input(scan(part,2,";"),best32.);
    Price   = input(scan(part,3,";"),??best32.)<>0;

    output;
  end;
run;
proc print;
run;

Bart