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.
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
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;
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.