Another approach, which in effect is a manual transpose and adding the _name_ which transpose will use to rename the output variables: data have; input _col1 $ _col2 $ wk1 wk2; cards; A A1 10 20 A B1 3 5 ; data helper; set have; array w(*) wk1 wk2; length _name_ $ 32; do i=1 to dim(w); _name_= catx('_',_col2,vname(w[i])); value=w[i]; output; end; drop i wk1 wk2; run; proc transpose data=helper out=want(drop=_name_); by _col1; var value; run; May be of interest if have a more complex naming scheme involved.   Note: Assumes the Have data is actually sorted by _col1 and that the combination of _col2 and the wk variable names will not create duplicates. ... View more

@amar8581980 wrote: @Kurt_Bremser  yes exactly because after the data dump in my excel 'Retail' tab, i have another tab with fancy formatting that using mapping into the retail tab to make numbers look fancy. and hence i want that excel to be converted into pdf Do you have any idea of how many ways SAS Proc Report, Tabulate or Print can "make numbers look fancy"?   Once upon a time I inherited a bunch of reports that used Excel data on one sheet and "fancy" formatting on another. And then the boss wanted a change to some elements reported. After I found a number of buried errors in a number of cells that "make the numbers look pretty", actually graphs, and fixed that stuff plus the additions the boss wanted I had about two weeks of work.   Starting from scratch I could make the same reports and graphs in SAS in about 4 hours. And don't have to look for silly formulas someone introduced where they modified an "annual" report to a quarterly one by dividing some numbers by 4. ... View more