topic Re: Table transformation in SAS Programming

Table transformation

dincooo — Mon, 05 Feb 2018 17:48:16 GMT

Hi everyne,

Suppose that I have a data like this:

(It shows company A made 4 times transaction to company B, company C made 1 time transaction to company D etc..)

A B 4

C D 1

A E 3

I need to transform this data like this:

ID Target

1 A

1 B

2 A

2 B

3 A

3 B

4 A

4 B

---------4 (A,B)

5 C

5 D

---------1 (C,D)

6 A

6 E

7 A

7 E

8 A

8 E

----------3 (A, E)

How can I do that?

Thank you very much!

Onur

Re: Table transformation

MINX — Mon, 05 Feb 2018 18:19:45 GMT

Check out. Hope this is what you want

data have;
input col1 :$ col2 :$ n;
datalines;
A B 4
C D 1
A E 3
;
run;

data want;
	set have;
	do id=1 to n;
		target=col1;
		output;
		target=col2;
		output;
	end;
	keep id target;
run;

Re: Table transformation

novinosrin — Mon, 05 Feb 2018 18:49:03 GMT

data have;
input var1 $ var2 $ var3;
datalines;
A B 4
C D 1
A E 3
;

data want;
set have;
retain _id;
if _n_=1 then do;
do id =1 to var3;
 target=var1;
 output;
  target=var2;
 output;
end;
_id=id;
end;
else do;
do id= _id by 1 until(id=_id+var3-1);
target=var1;
 output;
  target=var2;
 output;
end;
_id=id+1;
end;
keep id target;
run;