topic Re: long to wide in ODS and Base Reporting

long to wide

SASPhile — Fri, 04 Dec 2015 17:06:36 GMT

data gamdist; input cust_xref_id 1-6 Fico 9-12 rsn_cd1 13-15 rsn_cd2 17-19 Yr_Month $21-28; cards; 15013 668 39 18 2014-01 15013 668 39 18 2014-02 15013 668 39 18 2014-03 15013 670 39 18 2014-04 15013 678 39 18 2014-05 15013 638 38 20 2014-07 15013 635 38 20 2014-08 15013 635 38 20 2014-09 15013 643 38 20 2014-10 15013 643 38 20 2014-11 15013 648 38 24 2014-12 15013 648 38 24 2015-01 15013 648 38 24 2015-02 15013 651 38 24 2015-04 15013 653 38 24 2015-05 15013 653 38 24 2015-07 15013 654 38 24 2015-08 15013 661 38 24 2015-09 15013 662 38 24 2015-10 15013 662 38 24 2015-11 ; run; how to convert the above data to single record from minimum yr_month to maximum as below cust_xref_id fico_2014_01 rsn_cd1_2014_01 rsn_cd2_2014_01 fico_2014_02 rsn_cd1_2014_02 rsn_cd2_2014_02 ..........so on .....

Re: long to wide

Reeza — Fri, 04 Dec 2015 17:12:36 GMT

Please Please, format your code.
Use the little notepad Icon at the top of the window to enter code.

Re: long to wide

SASPhile — Fri, 04 Dec 2015 17:27:21 GMT

Added attachment for the same

Re: long to wide

Reeza — Fri, 04 Dec 2015 17:46:46 GMT

There's no attachment, and I'm not a fan of downloading files.

Re: long to wide

ndp — Fri, 04 Dec 2015 18:18:15 GMT

use 3 proc transpose with statement id year and option prefix=<varname_>. Then merge the 3 together by cust num.

Re: long to wide

SASPhile — Fri, 04 Dec 2015 18:18:39 GMT

data gamdist;
input cust_xref_id 1-6	Fico 9-12	rsn_cd1 13-15	rsn_cd2	17-19 Yr_Month $21-28;
cards;
15013	668	39	18	2014-01
15013	668	39	18	2014-02
15013	668	39	18	2014-03
15013	670	39	18	2014-04
15013	678	39	18	2014-05
15013	638	38	20	2014-07
15013	635	38	20	2014-08
15013	635	38	20	2014-09
15013	643	38	20	2014-10
15013	643	38	20	2014-11
15013	648	38	24	2014-12
15013	648	38	24	2015-01
15013	648	38	24	2015-02
15013	651	38	24	2015-04
15013	653	38	24	2015-05
15013	653	38	24	2015-07
15013	654	38	24	2015-08
15013	661	38	24	2015-09
15013	662	38	24	2015-10
15013	662	38	24	2015-11
;
run;


how to convert the above data to single record from minimum yr_month to maximum as below 

cust_xref_id fico_2014_01 rsn_cd1_2014_01 rsn_cd2_2014_01 fico_2014_02 rsn_cd1_2014_02 rsn_cd2_2014_02 ..........so on .....

Re: long to wide

hbi — Fri, 04 Dec 2015 19:20:41 GMT

This implements contributor ndp's suggestion. It also handles reordering of columns to the way you want them to appear.

Beware that the variables created by PROC TRANSPOSE, regrettably have a hyphen in them: Fico_2014-01, rsn_cd1_2014-01, rsn_cd2_2014-01, etc.

Enjoy!

data gamdist;
  input cust_xref_id 1-6 Fico 9-12 rsn_cd1 13-15 rsn_cd2 17-19 Yr_Month $21-28;
  cards;
15013   668 39  18  2014-01 
15013   668 39  18  2014-02 
15013   668 39  18  2014-03 
15013   670 39  18  2014-04 
15013   678 39  18  2014-05 
15013   638 38  20  2014-07 
15013   635 38  20  2014-08 
15013   635 38  20  2014-09 
15013   643 38  20  2014-10 
15013   643 38  20  2014-11 
15013   648 38  24  2014-12 
15013   648 38  24  2015-01 
15013   648 38  24  2015-02 
15013   651 38  24  2015-04 
15013   653 38  24  2015-05 
15013   653 38  24  2015-07 
15013   654 38  24  2015-08 
15013   661 38  24  2015-09 
15013   662 38  24  2015-10 
15013   662 38  24  2015-11
;
run;


%MACRO DoTranspose(variable_name, dataset_name);

  proc transpose data=work.gamdist
    out=work.&dataset_name(drop=_NAME_) prefix=&variable_name._;
    by cust_xref_id;
    id Yr_Month;
    var &variable_name;
  run;

%MEND;

%DoTranspose(Fico,    step1);
%DoTranspose(rsn_cd1, step2);
%DoTranspose(rsn_cd2, step3);


PROC SQL;
  /* come up with a way to reorder variables */
  /* the variable names have a hyphen (-) so variable names must look something like this: "Fico_2014-01"n */
  SELECT CAT('"Fico_', STRIP(Yr_Month), '"n ', '"rsn_cd1_', STRIP(Yr_Month), '"n ', '"rsn_cd2_', STRIP(Yr_Month), '"n ')
  INTO :ordered_variable_list SEPARATED BY ' ' 
  FROM gamdist
  ORDER BY Yr_Month;
QUIT;
%put &ordered_variable_list;


DATA work.combined;
  /* reorder variables */
  ATTRIB cust_xref_id &ordered_variable_list label='';
  MERGE work.step1 work.step2 work.step3;
  BY cust_xref_id;
RUN;

Re: long to wide

PGStats — Fri, 04 Dec 2015 21:33:32 GMT

Make your data long and do a single transpose:


data temp;
set gamdist; 
array v{*} Fico -- rsn_cd2;
suffix = translate(Yr_Month, "_", "-");
do i = 1 to dim(v);
    var = catx("_", vname(v{i}), suffix);
    value = v{i};
    output;
    end;
run;

proc transpose data=temp out=want(drop=_name_);
by cust_xref_id; /* add NOTSORTED if needed */
var value;
id var;
run;