Rename variables from values in a narrow table

[Epigurl]

Hi SAS Community,

I have 2 datasets. The wide data set has customer sales data by week with column names i.e. Cust_ID, wk_001, wk_002, etc. and the narrow dataset holds the actual week date with column name Col_label. See below for examples. I tried to transpose the wide dataset so the WK_001, WK_002, etc columns are now values and the variable name is Date. Additionally, I want to rename the values in the new "Date" column with the values in the narrow data set column "Col_label". 

proc transpose data=wide out=long;
	by cust_id;
	var wk_001-wk_xx;
run;

Narrow Data

Typ              Column             Col_label

Weekly        WK_001            Sep_17_08

Weekly        WK_002            Sep_17_01

Wide Data

Cust_ID      WK_001             WK_002

1001            $$                       $$

Essentially, the new data set will look like:

New data set

Cust_ID     Date                  Sales

1001           Sep_17_08        $$

1001           Sep_17_01        $$

Thanks in advance for your help!

[Astounding]

I would lean toward a single DATA step:

data want;

if _n_=1 then do;

   array labels {52} $ 9;

   do until (done);

      set narrow (rename=(column=week_char)) end=done;

      week_no = input(substr(week_char, 4), 3.);

      labels{week_no} = Col_Label;

   end;

end;

For illustration, I assumed 52 weeks, but that is easily changeable.  At this point, you have the originally narrow data set in wide form.  The same DATA step continues:

set wide;

array amounts {*} WK_:;

do i=1 to dim(amounts);

   sales = amounts{i};

   week_no = input(substr(vname(amounts{i}),4), 3.);

   Date = labels{week_no};

   output;

end;

keep Cust_ID Date Sales;

run;

It's untested code, so might need some tweaking. 

[ChrisNZ · Accepted solution]

Like this?

data N;
input TYP $ COLUMN $ COL_LABEL : $9. ;
cards;
Weekly WK_001 Sep_17_08
Weekly WK_002 Sep_17_01
run;
data W;
input CUST_ID  $  WK_001 WK_002 ;
cards;
1001 23 24
run;  
proc transpose data=W out=L;
	by CUST_ID;
	var WK_001-WK_002;
run;
proc sql;
  select CUST_ID, COL_LABEL 'Date', COL1 as SALES 'Sales'  from L,N where L._NAME_=N.COLUMN; 
quit;

CUST_ID	Date	Sales
1001	Sep_17_08	23
1001	Sep_17_01	24

[Epigurl]

Thanks @ChrisNZ for your help with this SAS code!

[Tom]

Do you just want to use the "narrow" table to assign labels to the columns in the "wide" table?

data narrow ;
 length typ column col_label $32 ;
 input typ column col_label;
cards;
Weekly WK_001 Sep_17_08
Weekly WK_002 Sep_17_01
;
 
data wide ;
  input Cust_id $ WK_001 WK_002 ;
cards;
1001 1 2
1002 2 3
;

proc sql noprint ;
  select catx('=',column,quote(trim(col_label)))
    into :labels separated by ' '
    from narrow
  ;
quit;

proc transpose data=wide out=want (rename=(col1=Sales));
  by cust_id;
  var wk_001 wk_002;
  label &labels ;
run;

[ShiroAmada]

Try this.....

proc sort data=WIDE;
  by CUSTID;
run;


proc transpose data=WIDE out=W_WIDE(rename= (_NAME_=COLUMN COL1=SALES) );
  var WK_:;
  by CUSTID;
run;

proc sql;
  create table WANT as
  select 
    A.CUSTID,
    B.LABEL as DATE,
    A.SALES 
  from W_WIDE a left join NARROW b
  on upcase(A.COLUMN)=upcase(B.COLUMN);

quit;

Note that the values for your variable COLUMN in your source tables may have different lengths.  I suggest you standardize using character functions such as upcase, strip...

Hope this helps.

[Epigurl]

Thanks @ShiroAmada, your code was very similar to ChrisNZ, so I am sure it would have yielded a successful result as well!