BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Riya88
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

6 REPLIES 6
Tom
Super User Tom
Super User

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
Fluorite | Level 6

Thanks Tom. It worked like a charm.

Ksharp
Super User

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
Obsidian | Level 7

you missed 'by id;' right?

Ksharp
Super User

Nope. Did you notice what Tom said to OP ?

JVarghese
Obsidian | Level 7

yes , got it.

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
  • 6 replies
  • 1340 views
  • 5 likes
  • 4 in conversation