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.
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;
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;
Thanks Tom. It worked like a charm.
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
you missed 'by id;' right?
Nope. Did you notice what Tom said to OP ?
yes , got it.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.