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
... View more
