## Table transformation

Solved
Occasional Contributor
Posts: 17

# Table transformation

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

Accepted Solutions
Solution
‎02-07-2018 02:09 AM
Contributor
Posts: 29

## Re: Table transformation

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;
``````

All Replies
Solution
‎02-07-2018 02:09 AM
Contributor
Posts: 29

## Re: Table transformation

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;
``````
Super User
Posts: 2,065

## Re: Table transformation

``````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;``````
☑ This topic is solved.