topic Re: Data Restructuring in SAS Programming

Data Restructuring

Riya88 — Sat, 04 Apr 2015 11:09:22 GMT

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.

Re: Data Restructuring

Tom — Sat, 04 Apr 2015 13:26:01 GMT

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;

Re: Data Restructuring

Ksharp — Sat, 04 Apr 2015 14:49:57 GMT

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

Re: Data Restructuring

JVarghese — Sat, 04 Apr 2015 20:33:03 GMT

you missed 'by id;' right?

Re: Data Restructuring

Ksharp — Sun, 05 Apr 2015 09:23:20 GMT

Nope. Did you notice what Tom said to OP ?

Re: Data Restructuring

JVarghese — Tue, 07 Apr 2015 22:12:12 GMT

yes , got it.

Re: Data Restructuring

Riya88 — Sun, 12 Apr 2015 09:02:56 GMT

Thanks Tom. It worked like a charm.