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

RaviSPR — Sun, 23 Jan 2022 19:27:12 GMT

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;

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

Re: Splitting Values of a String variable based on semicolon & add those record to the same tabl

yabwon — Wed, 26 Jan 2022 09:51:39 GMT

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

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

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

Re: Splitting Values of a String variable based on semicolon & add those record to the same tabl