The SAS Output Delivery System and reporting techniques

long to wide

Reply
Super Contributor
Posts: 625

long to wide

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 .....
Grand Advisor
Posts: 16,393

Re: long to wide

Please Please, format your code.
Use the little notepad Icon at the top of the window to enter code.
Super Contributor
Posts: 625

Re: long to wide

Added attachment for the same
Grand Advisor
Posts: 16,393

Re: long to wide

There's no attachment, and I'm not a fan of downloading files.
Super Contributor
Posts: 625

Re: long to wide

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 .....
Respected Advisor
Posts: 4,606

Re: long to wide

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;
PG
Contributor ndp
Contributor
Posts: 61

Re: long to wide

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

Contributor hbi
Contributor
Posts: 66

Re: long to wide

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! Robot Happy

 

 

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;

 

Post a Question
Discussion Stats
  • 7 replies
  • 617 views
  • 0 likes
  • 5 in conversation