Data Restructuring

[Riya88]

Hi Team,

I have data for a specific period of time (let's say table A). I want previous 6 months data to be merged with table A.

Table A looks like below :

ID	Q1	Q2	Q3	Q4
0058	3	4	6	1
0091	6	2	3	1
0028	4	1	5	1
0058	2	4	4	1
0087	5	1	4	5

6 months' data exist in a single table (say, table B) and it is shown below :

PD is the end period date. ID is the unique code.

PD	ID	Q1	Q2	Q3	Q4
30-Apr-15	0058	2	1	6	1
30-Apr-15	0091	5	2	3	1
30-Apr-15	0028	1	1	5	1
30-Apr-15	0058	3	4	4	1
30-Apr-15	0087	6	1	4	5
31-Mar-15	0058	4	5	4	6
31-Mar-15	0091	3	6	3	1
31-Mar-15	0028	2	5	4	3
31-Mar-15	0058	6	6	1	6
31-Mar-15	0087	6	5	5	3
28-Feb-15	0058	1	4	4	3
28-Feb-15	0091	4	2	3	2
28-Feb-15	0028	1	2	2	6
28-Feb-15	0058	1	3	5	4
28-Feb-15	0087	3	6	1	4

I want to merge table A with the table B based on ID column. But, data shoule dd be restructured on the basis of PD column.

The desired output is shown below :

ID	Q1	Q2	Q3	Q4	Q1_Apr	Q2_Apr	Q3_Apr	Q4_Apr	Q1_Mar	Q2_Mar	Q3_Mar	Q4_Mar
0058	3	4	6	1	2	1	6	1	4	5	4	6
0091	6	2	3	1	5	2	3	1	3	6	3	1
0028	4	1	5	1	1	1	5	1	2	5	4	3
0058	2	4	4	1	3	4	4	1	6	6	1	6
0087	5	1	4	5	6	1	4	5	6	5	5	3

Columns for Feb data should be added in a similar way that March and April columns are updated in the above table.

[Tom · Accepted solution]

You have ID 58 twice in your data.  How can you tell them apart?

data A;

  input ID $ Q1-Q4 ;

cards;

0058A 3     4     6     1

0091  6     2     3     1

0028  4     1     5     1

0058B 2     4     4     1

0087  5     1     4     5

;;;;

proc sort data=A;

  by id;

run;

data B;

  informat pd date11. ;

  input pd id $ q1-q4 ;

  format pd date11. ;

  put (_all_) (=);

cards;

30-Apr-15   0058A 2     1     6     1

30-Apr-15   0091  5     2     3     1

30-Apr-15   0028  1     1     5     1

30-Apr-15   0058B 3     4     4     1

30-Apr-15   0087  6     1     4     5

31-Mar-15   0058A 4     5     4     6

31-Mar-15   0091  3     6     3     1

31-Mar-15   0028  2     5     4     3

31-Mar-15   0058B 6     6     1     6

31-Mar-15   0087  6     5     5     3

28-Feb-15   0058A 1     4     4     3

28-Feb-15   0091  4     2     3     2

28-Feb-15   0028  1     2     2     6

28-Feb-15   0058B 1     3     5     4

28-Feb-15   0087  3     6     1     4

;;;;

proc sort data=B;

  by id pd;

run;

proc transpose data=B out=step1 ;

  by id pd ;

  var q1-q4 ;

run;

proc sort data=step1; by id _name_ pd ; run;

proc transpose data=step1 out=step2 (drop=_name_) delim=_;

  by id ;

  id _name_ pd ;

  format pd monname3. ;

  var col1;

run;

data want ;

merge A step2;

by id ;

run;

proc print; run;

[Riya88]

Thanks Tom. It worked like a charm.

[Ksharp]

If table a and b have the same order like your original table did .

data A;
  input ID $ Q1-Q4 ;
cards;
0058 3     4     6     1
0091  6     2     3     1
0028  4     1     5     1
0058 2     4     4     1
0087  5     1     4     5
;;;;

data B;
  informat pd date11. ;
  input pd id $ q1-q4 ;
  format pd date11. ;
cards;
30-Apr-15   0058 2     1     6     1
30-Apr-15   0091  5     2     3     1
30-Apr-15   0028  1     1     5     1
30-Apr-15   0058 3     4     4     1
30-Apr-15   0087  6     1     4     5
31-Mar-15   0058 4     5     4     6
31-Mar-15   0091  3     6     3     1
31-Mar-15   0028  2     5     4     3
31-Mar-15   0058 6     6     1     6
31-Mar-15   0087  6     5     5     3
28-Feb-15   0058 1     4     4     3
28-Feb-15   0091  4     2     3     2
28-Feb-15   0028  1     2     2     6
28-Feb-15   0058 1     3     5     4
28-Feb-15   0087  3     6     1     4
;;;;

data want;
 merge a 
 b(where=(month(pd)=4) rename=( q1-q4=Apr_a1-Apr_a4))
 b(where=(month(pd)=3) rename=( q1-q4=Mar_a1-Mar_a4))
 b(where=(month(pd)=2) rename=( q1-q4=Feb_a1-Feb_a4)) ;
 drop pd;
run;

Xia Keshan

[JVarghese]

you missed 'by id;' right?

[Ksharp]

Nope. Did you notice what Tom said to OP ?

[JVarghese]

yes , got it.