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!
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 |
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.
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 |
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;
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.