BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Epigurl
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

6 REPLIES 6
Astounding
PROC Star

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
Tourmaline | Level 20

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
Calcite | Level 5
Thanks @ChrisNZ for your help with this SAS code!
Tom
Super User Tom
Super User

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

ShiroAmada
Lapis Lazuli | Level 10

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
Calcite | Level 5
Thanks @ShiroAmada, your code was very similar to ChrisNZ, so I am sure it would have yielded a successful result as well!

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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