BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
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 .....
7 REPLIES 7
Reeza
Super User
Please Please, format your code.
Use the little notepad Icon at the top of the window to enter code.
SASPhile
Quartz | Level 8
Added attachment for the same
Reeza
Super User
There's no attachment, and I'm not a fan of downloading files.
SASPhile
Quartz | Level 8
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 .....
PGStats
Opal | Level 21

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
ndp
Quartz | Level 8 ndp
Quartz | Level 8

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

hbi
Quartz | Level 8 hbi
Quartz | Level 8

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1693 views
  • 0 likes
  • 5 in conversation