I think this is what you are looking for. Keep in mind if the dataset is not sorted this way it will not work: data have; infile cards dsd; input Brand $ id org_id sales; cards; A1,1,111,3252 A2,2,111,2859 A3,3,111,3539 A4,4,111,3890 A5,5,111,3944 A6,6,111,3825 A7,7,111,4711 A8,8,111,3178 A9,9,111,4992 A10,10,111,4020 A11,11,111,2589 A12,12,111,1559 B 1,13,111,1949 B 2,14,, B 3,15,, B 4,16,, B 5,17,, B 6,18,, B 7,19,, B 8,20,, B 9,21,, B 10,22,111,3764 B 11,23,111,1402 B 12,24,111,1723 C1,25,111,1086 C2,26,111,4767 C3,27,111,1455 C4,28,111,3552 C5,29,111,4862 C6,30,, C7,31,, C8,32,, C9,33,, C10,34,, C11,35,111,3482 C12,36,111,1377 D1,37,111,2803 D2,38,111,3021 D3,39,111,3074 D4,40,111,1246 D5,41,, D6,42,, D7,43,, D8,44,111,3690 D9,45,111,1022 D10,46,111,4089 D11,47,111,1603 D12,48,111,3898 A1,1,222,3252 A2,2,222,2859 A3,3,222,3539 A4,4,222,3890 A5,5,222,3944 A6,6,222,3825 A7,7,222,4711 A8,8,222,3178 A9,9,, A10,10,, A11,11,, A12,12,, B 1,13,, B 2,14,, B 3,15,222,3539 B 4,16,222,3890 B 5,17,222,3944 B 6,18,222,3825 B 7,19,222,4711 B 8,20,222,3178 B 9,21,222,5435 B 10,22,222,3764 B 11,23,222,1402 B 12,24,222,1723 C1,25,222,1086 C2,26,222,4767 C3,27,222,1455 C4,28,222,3552 C5,29,222,4862 C6,30,222,1949 C7,31,222,2859 C8,32,222,3539 C9,33,222,3890 C10,34,222,3944 C11,35,222,3825 C12,36,222,1377 D1,37,222,2803 D2,38,222,3021 D3,39,, D4,40,, D5,41,, D6,42,, D7,43,, D8,44,, D9,45,, D10,46,222,3825 D11,47,222,1603 D12,48,222,3898 A1,1,333,3252 A2,2,333,2859 A3,3,333,3539 A4,4,333,3890 A5,5,333,3944 A6,6,333,3825 A7,7,333,4711 A8,8,333,3178 A9,9,333,4992 A10,10,333,4020 A11,11,333,2589 A12,12,333,1559 B 1,13,333,1949 B 2,14,333,3890 B 3,15,333,3944 B 4,16,333,3825 B 5,17,333,4711 B 6,18,333,3178 B 7,19,333,234 B 8,20,333,5346 B 9,21,333,3566 B 10,22,, B 11,23,, B 12,24,, C1,25,, C2,26,, C3,27,, C4,28,, C5,29,333,4862 C6,30,333,3898 C7,31,333,3252 C8,32,333,2859 C9,33,333,3539 C10,34,333,2345 C11,35,333,3482 C12,36,333,1377 D1,37,333,2803 D2,38,333,3021 D3,39,333,3074 D4,40,, D5,41,, D6,42,, D7,43,, D8,44,, D9,45,333,1022 D10,46,333,4089 D11,47,333,1603 D12,48,333,3898 A1,1,444,3252 A2,2,444,2859 A3,3,444,3539 A4,4,444,3890 A5,5,444,3944 A6,6,444,3825 A7,7,444,4711 A8,8,444,3178 A9,9,444,4992 A10,10,444,4020 A11,11,444,2589 A12,12,444,1559 B 1,13,444,1949 B 2,14,444,1949 B 3,15,444,2859 B 4,16,444,3539 B 5,17,444,3890 B 6,18,444,3944 B 7,19,444,3825 B 8,20,, B 9,21,, B 10,22,, B 11,23,, B 12,24,, C1,25,, C2,26,, C3,27,, C4,28,, C5,29,444,4862 C6,30,444,1949 C7,31,444,2859 C8,32,444,3539 C9,33,444,3890 C10,34,444,3944 C11,35,444,3825 C12,36,444,1377 D1,37,444,2803 D2,38,444,3021 D3,39,, D4,40,, D5,41,, D6,42,, D7,43,, D8,44,444,3690 D9,45,444,1022 D10,46,444,4089 D11,47,444,1603 D12,48,444,3898 A1,1,555,3252 A2,2,555,2859 A3,3,555,3539 A4,4,555,3890 A5,5,555,3944 A6,6,555,3825 A7,7,555,4711 A8,8,555,3178 A9,9,555,4992 A10,10,555,4020 A11,11,555,2589 A12,12,555,1559 B 1,13,555,1949 B 2,14,555, B 3,15,555, B 4,16,555, B 5,17,555, B 6,18,555, B 7,19,555, B 8,20,555, B 9,21,555, B 10,22,555,3764 B 11,23,555,1402 B 12,24,555,1723 C1,25,555,1086 C2,26,555,4767 C3,27,555,1455 C4,28,555,3552 C5,29,555,4862 C6,30,555, C7,31,555, C8,32,555, C9,33,555, C10,34,555, C11,35,555,3482 C12,36,555,1377 D1,37,555,2803 D2,38,555,3021 D3,39,555,3074 D4,40,555,1246 D5,41,555, D6,42,555, D7,43,555, D8,44,555,3690 D9,45,555,1022 D10,46,555,4089 D11,47,555,1603 D12,48,555,3898 ; run; data want(rename=(org=org_id)); retain org; set have; if org_id ne . then org=org_id; drop org_id; run;
... View more
