Making a long merged data set broad

[Jannie_D]

Thank you. That makes sense. But does the code take into consideration that price and date are paired. so only one date-variable match one price-variable and so on? It seems to me that I might need a step for that first?

[Kurt_Bremser]

The code works on the strict assumption that date/price variables are paired on their sequence numbers.

Note that because of the use of date: and price:, the order of the variables is important for a correct pairing; see this short illustration of the use of variable lists in ARRAY statements:

data have;
input a1 a2 a3 b1 b3 b2;
datalines;
1 2 3 4 5 6
;

data test;
set have;
array for_a {*} a:;
array for_b_1 {*} b:; /* ordered as positioned in the dataset */
array for_b_2 {*} b1-b3; /* ordered by name */
array for_b_3 {*} b1--b3; /* ordered by position, missing one item */
dim3 = dim(for_b_3);
put dim3;
do i = 1 to 3;
  put i=;
  name_a = vname(for_a{i});
  put name_a=;
  name_b_1 = vname(for_b_1{i});
  put name_b_1=;
  name_b_2 = vname(for_b_2{i});
  put name_b_2=;
  if i le 2
  then do;
    name_b_3 = vname(for_b_3{i});
    put name_b_3=;
  end;
end;
run;

The only method that forces a correct order by variable name, regardless of physical position in the dataset, is the x1-xX notation.

[Jannie_D]

It looks like my data set is to big for the proc report statement. It made the program crash a few times. Is there something else I can do instead

[Kurt_Bremser]

The fact that you wanted a total sum line for every PNR let me assume you need a report. Inserted summary observations in datasets usually makes those datasets unusable for further processing.

But you can easily summarize from the want dataset:

proc summary data=want;
by pnr notsorted;
var total_cost;
output
  out=sum (drop=_type_ rename=(_freq_=num_years))
  sum()=
;
run;