sas - Sections and subsections in the same column with an ID, how to change structure -

i have dataset has sections , subsections in same column identified id, , need have in different structure divided in columns generate report. if don’t, proc report generate duplicate information. tried retain option keep last subsection of each 1 result wasn't expected.

here dataset have , dataset want , report want

have:

data have; infile datalines delimiter=',' dsd; input id $ concept : $15. amount 15.; datalines; 1,store1,85.5 1.1,vend1,43 1.1.1,income,25 1.1.1.1,income 1,10 1.1.1.2,income 2,5 1.1.1.3,income 3,10 1.1.2,sales,18 1.1.2.1,sales 1,12 1.1.2.2,sales 2,6 1.2,vend2,42.5 1.2.1,income,2.5 1.2.1.1,comission 1,2.5 1.2.2,sales,40 1.2.2.1,sale 1,15 1.2.2.2,sale 2,15 1.2.2.3,sale 3,10 2,store 2,75.6 2.1,vend 1,18.3 2.1.1,income,15 2.1.1.1,income 1,7 2.1.1.2,income 2,8 2.1.2,sales,3.3 2.1.2.1,sales 1,3.3 2.2,vend 2,57.3 2.2.1,income,7.3 2.2.1.1,comission 1,5 2.2.1.2,comission 2,2.3 2.2.2,sales,0 2.2.3,others,50 ; run; 

want:

data want; infile datalines  delimiter=',' dsd; input store $ vend $ type_1 : $15. type_2 : $15. amount 15.; datalines; store 1,vend1,income,income 1,10 store 1,vend1,income,income 2,5 store 1,vend1,income,income 3,10 store 1,vend1,sales,sales 1,12 store 1,vend1,sales,sales 2,6 store 1,vend2,income,comission 1,2.5 store 1,vend2,sales,sale 1,15 store 1,vend2,sales,sale 2,15 store 1,vend2,sales,sale 3,10 store 2,vend 1,income,income 1,7 store 2,vend 1,income,income 2,8 store 2,vend 1,sales,sales 1,3.3 store 2,vend 2,income,comission 1,5 store 2,vend 2,income,comission 2,2.3 store 2,vend 2,sales,sales,0 store 2,vend 2,others ,others ,50 ; run; 

final report:

proc report data=want nowindows spanrows missing style(report)=[rules=groups frame=box]; column store vend type_1 type_2 (amount); define  store / group  order=data; define  vend / group  order=data; define  type_1 / group order=data; define  type_2 / group order=data; define amount /analysis sum format=dollar15.2; rbreak after/summarize; run; 

thanks.

data want;     length store $7. vend $6. type_1 $15. type_2 $15. amount 8.;     set have;     length_id = length(id);     retain store vend type_1;     if length_id = 1 store = concept;     if length_id = 3 vend = concept;     if length_id = 5 type_1 = concept;     if length_id = 7 type_2 = concept;     if length_id = 7 or amount = 0 or concept = 'others';     if amount = 0 or concept = 'others' type_2 = concept;     drop id concept length_id; run;