Help using Base SAS procedures

Transposing Data (a bit more complicated this time around)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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;

idItem_nameSequenceType_name
01ABC1company
01domestic1direct
01cash1type
0110001classA
015001classB
011indirect
011type
011classA
011classB
01ABC2company
01international2direct
01bond2type
0120002classA
0115002classB
012indirect
012type
012classA
012classB
01ABC3company
01domestic3direct
01property3type
0150003classA
0135003classB
013indirect
013type
013classA
013classB
02DEF1company
021direct
021type
021classA
021classB
02domestic1indirect
02cash1type
0240001classA
0230001classB
02DEF2company
022direct
022type
022classA
022classB
02international2indirect
02bond2type
0270002classA
0265002classB
02DEF3company
023direct
023type
023classA
023classB
02domestic3indirect
02property3type
0290003classA
0285003classB

and trying to transform it into the following;

idcompanytypevalue
01ABCdirect=domestic, type=cash, classA>01000
01ABCdirect=domestic, type=cash, classB>0500
01ABCdirect=international, type=bond, classA>02000
01ABCdirect=international, type=bond, classB>01500
01ABCdirect=domestic, type=property, classA>05000
01ABCdirect=domestic, type=property, classB>03500
02DEFindirect=domestic, type=cash, classA>04000
02DEFindirect=domestic, type=cash, classB>03000
02DEFindirect=international, type=bond, classA>07000
02DEFindirect=international, type=bond, classB>06500
02DEFindirect=domestic, type=property, classA>09000
02DEFindirect=domestic, type=property, classB>08500

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: 9,681

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

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

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

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: 9,681

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

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

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 210 views
  • 3 likes
  • 3 in conversation