DATA Step, Macro, Functions and more

Data Restructuring

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Data Restructuring

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 :

IDQ1Q2Q3Q4
00583461
00916231
00284151
00582441
00875145

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.

PDIDQ1Q2Q3Q4
30-Apr-1500582161
30-Apr-1500915231
30-Apr-1500281151
30-Apr-1500583441
30-Apr-1500876145
31-Mar-1500584546
31-Mar-1500913631
31-Mar-1500282543
31-Mar-1500586616
31-Mar-1500876553
28-Feb-1500581443
28-Feb-1500914232
28-Feb-1500281226
28-Feb-1500581354
28-Feb-1500873614

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 :

IDQ1Q2Q3Q4Q1_AprQ2_AprQ3_AprQ4_AprQ1_MarQ2_MarQ3_MarQ4_Mar
0058346121614546
0091623152313631
0028415111512543
0058244134416616
0087514561456553

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


Accepted Solutions
Solution
‎04-04-2015 09:26 AM
Super User
Super User
Posts: 7,050

Re: Data Restructuring

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;

View solution in original post


All Replies
Solution
‎04-04-2015 09:26 AM
Super User
Super User
Posts: 7,050

Re: Data Restructuring

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;

Occasional Contributor
Posts: 6

Re: Data Restructuring

Thanks Tom. It worked like a charm.

Super User
Posts: 10,028

Re: Data Restructuring

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

Contributor
Posts: 43

Re: Data Restructuring

you missed 'by id;' right?

Super User
Posts: 10,028

Re: Data Restructuring

Posted in reply to JVarghese

Nope. Did you notice what Tom said to OP ?

Contributor
Posts: 43

Re: Data Restructuring

yes , got it.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 396 views
  • 5 likes
  • 4 in conversation