Transposing Data (a bit more complicated this time around)

[erickbernard]

Hi everyone,

I have a transposition problem which I can't figure out how to solve. So SAS forum, you are my only hope at this point.

Here we go. Suppose I have the following data;

id	Item_name	Sequence	Type_name
01	ABC	1	company
01	domestic	1	direct
01	cash	1	type
01	1000	1	classA
01	500	1	classB
01		1	indirect
01		1	type
01		1	classA
01		1	classB
01	ABC	2	company
01	international	2	direct
01	bond	2	type
01	2000	2	classA
01	1500	2	classB
01		2	indirect
01		2	type
01		2	classA
01		2	classB
01	ABC	3	company
01	domestic	3	direct
01	property	3	type
01	5000	3	classA
01	3500	3	classB
01		3	indirect
01		3	type
01		3	classA
01		3	classB
02	DEF	1	company
02		1	direct
02		1	type
02		1	classA
02		1	classB
02	domestic	1	indirect
02	cash	1	type
02	4000	1	classA
02	3000	1	classB
02	DEF	2	company
02		2	direct
02		2	type
02		2	classA
02		2	classB
02	international	2	indirect
02	bond	2	type
02	7000	2	classA
02	6500	2	classB
02	DEF	3	company
02		3	direct
02		3	type
02		3	classA
02		3	classB
02	domestic	3	indirect
02	property	3	type
02	9000	3	classA
02	8500	3	classB

and trying to transform it into the following;

id	company	type	value
01	ABC	direct=domestic, type=cash, classA>0	1000
01	ABC	direct=domestic, type=cash, classB>0	500
01	ABC	direct=international, type=bond, classA>0	2000
01	ABC	direct=international, type=bond, classB>0	1500
01	ABC	direct=domestic, type=property, classA>0	5000
01	ABC	direct=domestic, type=property, classB>0	3500
02	DEF	indirect=domestic, type=cash, classA>0	4000
02	DEF	indirect=domestic, type=cash, classB>0	3000
02	DEF	indirect=international, type=bond, classA>0	7000
02	DEF	indirect=international, type=bond, classB>0	6500
02	DEF	indirect=domestic, type=property, classA>0	9000
02	DEF	indirect=domestic, type=property, classB>0	8500

Notice, there is a structure in the data. If for example, type is direct, it can't be indirect in that sequence. What I'm struggling with is the "logic" I want to implement,

if  (type_name=direct and item_name=domestic) and type_name=classA then value=1,000.

I'll post the sample code for the dummy data above as well if it helps;

data have;

infile cards dsd;

  input id:$2. item_name :$13. sequence :$2. type_name :$8.;

cards;

01,ABC,1,company

01,domestic,1,direct

01,cash,1,type

01,1000,1,classA

01,500,1,classB

01,,1,indirect

01,,1,type

01,,1,classA

01,,1,classB

01,ABC,2,company

01,international,2,direct

01,bond,2,type

01,2000,2,classA

01,1500,2,classB

01,,2,indirect

01,,2,type

01,,2,classA

01,,2,classB

01,ABC,3,company

01,domestic,3,direct

01,property,3,type

01,5000,3,classA

01,3500,3,classB

01,,3,indirect

01,,3,type

01,,3,classA

01,,3,classB

02,DEF,1,company

02,,1,direct

02,,1,type

02,,1,classA

02,,1,classB

02,domestic,1,indirect

02,cash,1,type

02,4000,1,classA

02,3000,1,classB

02,DEF,2,company

02,,2,direct

02,,2,type

02,,2,classA

02,,2,classB

02,international,2,indirect

02,bond,2,type

02,7000,2,classA

02,6500,2,classB

02,DEF,3,company

02,,3,direct

02,,3,type

02,,3,classA

02,,3,classB

02,domestic,3,indirect

02,property,3,type

02,9000,3,classA

02,8500,3,classB

;

run;

Hopefully someone will be able to crack this.

[RW9]

Hi,

Just build up the string as you go along, retaining the values, then output at the end of the sequence number, something like:

data want;

set have;

length company $20 type $200;

retain id company type value;

by id sequence;

if first.sequence then company=item_name;

else do;

  if type_name in ("direct","cash") then type=strip(type)||type_name||"="||strip(item_name);

  if type_name="...

end;

if last.sequence then output;

run;

[Ksharp · Accepted solution]

How about this :

data have;

infile cards dsd;

  input id:$2. item_name :$13. sequence :$2. type_name :$8.;

cards;

01,ABC,1,company

01,domestic,1,direct

01,cash,1,type

01,1000,1,classA

01,500,1,classB

01,,1,indirect

01,,1,type

01,,1,classA

01,,1,classB

01,ABC,2,company

01,international,2,direct

01,bond,2,type

01,2000,2,classA

01,1500,2,classB

01,,2,indirect

01,,2,type

01,,2,classA

01,,2,classB

01,ABC,3,company

01,domestic,3,direct

01,property,3,type

01,5000,3,classA

01,3500,3,classB

01,,3,indirect

01,,3,type

01,,3,classA

01,,3,classB

02,DEF,1,company

02,,1,direct

02,,1,type

02,,1,classA

02,,1,classB

02,domestic,1,indirect

02,cash,1,type

02,4000,1,classA

02,3000,1,classB

02,DEF,2,company

02,,2,direct

02,,2,type

02,,2,classA

02,,2,classB

02,international,2,indirect

02,bond,2,type

02,7000,2,classA

02,6500,2,classB

02,DEF,3,company

02,,3,direct

02,,3,type

02,,3,classA

02,,3,classB

02,domestic,3,indirect

02,property,3,type

02,9000,3,classA

02,8500,3,classB

;

run;

data temp(keep=id sequence _type value);

set have(where=(type_name in ('classA' 'classB' ) and Item_name is not missing));

if input(Item_name,best32.) gt 0 then do; _type=catx('>',type_name,'0'); value=Item_name;end;

  else do; _type=catx('<=',type_name,'0'); value=Item_name;end;

run;

data key(keep=id sequence t company) ;

merge have(keep=id sequence type_name item_name  where=(type_name in ('direct' 'indirect'  'type'  ) and item_name is not missing))

       have(keep=id sequence type_name item_name rename=(item_name=company type_name=_type_name) where=(_type_name = 'company')) ;

by id sequence;

length t $ 200;

retain t;

t=catx(',',t,cats(type_name,'=',item_name));

if last.sequence then do;output;call missing(t);end;

run;

data want;

merge temp key;

by id sequence;

type=catx(',',t,_type);

drop _type t;

run;

Xia Keshan

[erickbernard]

in retrospect, this is the way to go i think. there are more changes in the data i received. so hopefully i can modify this to work with that.