topic Re: Rename variables from values in a narrow table in SAS Programming

Rename variables from values in a narrow table

Epigurl — Thu, 28 Sep 2017 20:47:03 GMT

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!

Re: Rename variables from values in a narrow table

Astounding — Thu, 28 Sep 2017 21:13:28 GMT

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;

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.

Re: Rename variables from values in a narrow table

ChrisNZ — Thu, 28 Sep 2017 23:13:46 GMT

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

Re: Rename variables from values in a narrow table

Tom — Fri, 29 Sep 2017 00:26:06 GMT

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;

Re: Rename variables from values in a narrow table

ShiroAmada — Fri, 29 Sep 2017 01:56:45 GMT

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.

Re: Rename variables from values in a narrow table

Epigurl — Mon, 02 Oct 2017 14:21:26 GMT

Thanks @ChrisNZ for your help with this SAS code!

Re: Rename variables from values in a narrow table

Epigurl — Mon, 02 Oct 2017 14:22:49 GMT

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