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.
... View more