Rename variables from values in a narrow table

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Rename variables from values in a narrow table

[ Edited ]

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!


Accepted Solutions
Solution
‎10-02-2017 10:17 AM
PROC Star
Posts: 1,925

Re: Rename variables from values in a narrow table

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

 

View solution in original post


All Replies
Super User
Posts: 5,987

Re: Rename variables from values in a narrow table

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. 

Solution
‎10-02-2017 10:17 AM
PROC Star
Posts: 1,925

Re: Rename variables from values in a narrow table

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

 

New Contributor
Posts: 3

Re: Rename variables from values in a narrow table

Thanks @ChrisNZ for your help with this SAS code!
Super User
Super User
Posts: 7,393

Re: Rename variables from values in a narrow table

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;

image.png

Frequent Contributor
Posts: 110

Re: Rename variables from values in a narrow table

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.

New Contributor
Posts: 3

Re: Rename variables from values in a narrow table

Posted in reply to ShiroAmada
Thanks @ShiroAmada, your code was very similar to ChrisNZ, so I am sure it would have yielded a successful result as well!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 139 views
  • 0 likes
  • 5 in conversation