SAS: converting narrow to wide dataset

Question

I'm doing the exercises from the SAS Programming 2 textbook.

I'm trying to convert this data:

Narrow Data set To a wide data-set like this:

Wide Data Set I'm also supposed to have an array in my data step and only output the variable customer_id and month1 through month12.

My code is the following:

Data customer_orders(keep=Customer_ID month1-month12);
set orion.order_summary;
by customer_id;
array month{12}  month1-month12; 
do i= 1 to 12;
if order_month = i then     
month{i}= sale_amt;
end;

run;

proc print data=customer_orders;
run;

My problem when I run this code is that the observations does not display all of the sale_amt values for customer_id in one observation but instead skips to the next line to display the second value found in the observation.

Any help would be greatly appreciated.

Note: I'm not allowed to post another link to what my output looks like.

You need to use RETAIN to keep variables across rows. Otherwise on each row the array vars are set to missing. You also need an explicit OUTPUT statement. — Reeza

Andrew Haynes Andrew Haynes · Accepted Answer · 2016-12-16T10:36:05

As was commented you need to set a retain statement to carry your values over to a next line as SAS resets values to missing during the processing step. Last.cust_id then takes only the last line per customer id, and this line should contain all your observations for that customer.

This will however retain them for all values afterwards until otherwise specified. So using first.cust_id you can set all the values to missing at each new customer id.

data test;

input Cust_id Month Sale_Amt;
Datalines;
 5 5 478
 5 6 126.8
 5 9 52.50
 5 12 33.8
 10 3 32.60
 10 1 200
 ;

run;

proc sort data = test out = test_sort;
  by cust_id month;
run;


data test2 ( drop  = month sale_amt i );

  set test_sort;
  by cust_id;

  array holder (*) Month1-Month12;

  retain Month1-Month12;

  do i = 1 to 12;
    if first.cust_id then holder{i} = .;
    if month = i     then holder{i} = sale_amt;
  end;

  if last.cust_id;

run;

SAS: converting narrow to wide dataset

1 Answers