Table transformation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

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;
	

View solution in original post


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;
	
PROC Star
Posts: 1,356

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 109 views
  • 0 likes
  • 3 in conversation