## Transposing Data (a bit more complicated this time around)

Solved
Occasional Contributor
Posts: 11

# Transposing Data (a bit more complicated this time around)

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.

Accepted Solutions
Solution
‎10-27-2014 08:06 AM
Super User
Posts: 10,784

## Re: Transposing Data (a bit more complicated this time around)

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

All Replies
Super User
Posts: 9,599

## Re: Transposing Data (a bit more complicated this time around)

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;

Solution
‎10-27-2014 08:06 AM
Super User
Posts: 10,784

## Re: Transposing Data (a bit more complicated this time around)

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

Occasional Contributor
Posts: 11

## Re: Transposing Data (a bit more complicated this time around)

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.

🔒 This topic is solved and locked.