topic Re: Transpose using Arrays in SAS Programming

Transpose using Arrays

Sathya3 — Mon, 06 May 2024 14:53:56 GMT

data two;
input name $ qtr amount;
datalines;
adam 1 10
adam 2 20
adam 3 30
adam 4 40
;
run;

Need a code to produce below output using array and not proc transpose

output must be :

name qtr1 qtr2 qtr3 qtr4
adam 10 20 30 40

Re: Transpose using Arrays

yabwon — Mon, 06 May 2024 14:58:59 GMT

data two;
input name $ qtr amount;
datalines;
adam 1 10
adam 2 20
adam 3 30
adam 4 40
bdam 1 10
bdam 2 20
bdam 4 40
cdam 1 10
cdam 2 20
cdam 3 30
;
run;


data want;
  set two;
  by name;

  array q[4] qtr1-qtr4 (4*.);

  q[qtr] = amount;

  if last.name;
  output;
  call missing(of q[*]);
  
  drop qtr amount;
run;
                         
proc print;
run;

This works, but do consider transpose.

Bart

Re: Transpose using Arrays

Patrick — Mon, 06 May 2024 15:01:26 GMT

Why not Proc Transpose? Below one option

data have;
  input name $ qtr amount;
datalines;
adam 1 10
adam 2 20
adam 3 30
adam 4 40
;
run;

data want(drop=_q amount);
  set have(rename=(qtr=_q));;
  by name;
  array qtr{4} 8;
  retain qtr;
  qtr[_q]=amount;
  if last.name then
    do;
      output;
      call missing(of qtr[*]);
    end;
run;

Re: Transpose using Arrays

PaigeMiller — Mon, 06 May 2024 16:24:45 GMT

Why not leave the data untransposed?

The only reason I can see you might want to have this data arranged with columns qtr1, qtr2, ... is for a report, and if that's what you want, do not transpose, use PROC REPORT which will work on untransposed data.

data two;
input name $ qtr amount;
datalines;
adam 1 10
adam 2 20
adam 3 30
adam 4 40
paul 1 23
paul 2 33
paul 3 41
paul 4 67
;

proc report data=two;
    columns ('Name' name) (' ' amount),qtr;
    define name/' ' group;
    define qtr/'Quarter' across;
    define amount/' ' sum;
run;