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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.