BookmarkSubscribeRSS Feed
sss
Fluorite | Level 6 sss
Fluorite | Level 6

Hi,

I have 3 tables and I want table 4 exactly in the way I haveshown down the page, objective is no information should be lost.

Problem is, Table2 and Table 3 has 1 column name common and1 column name different i.e. PID and DATE_1 and DATE_2 but Date_1 and Date_2providing same information so I want in table4 only one column with the name asDate.

Table1

PID

 

SEX

 

DOB

 

OCC

 

1

 

F

 

1985

 

EDU

 

2

 

M

 

1986

 

IT

 

3

 

M

 

1986

 

EDU

 

Table2

PID

 

Date_1

 

1

 

1999

 

2

 

1999

 

Table3

PID

 

Date_2

 

MS

 

2

 

2002

 

A

 

3

 

2000

 

B

 

I want this kind of table in sas;

Table4

PID

 

SEX

 

DOB

 

OCC

 

MS

 

DATE

 

1

 

F

 

1985

 

EDU

 

Null

 

1999

 

2

 

M

 

1986

 

IT

 

A

 

1999

 

2

 

M

 

1986

 

IT

 

A

 

2002

 

3

 

M

 

1986

 

EDU

 

B

 

2000

 

Waiting for reply its urgent. thnx in advence

2 REPLIES 2
Tom
Super User Tom
Super User

The problem is TABLE3.  You want to concatenate it with TABLE2 so that you get all the dates, but you also want to merge it by PID so that all records for the same PID get the value of the MS variable.

data table1 ;

  input pid sex $ dob occ $ ;

cards;

1 F 1985 EDU

2 M 1986 IT

3 M 1986 EDU

run;

data table2 ;

  input pid date_1 ;

cards;

1 1999

2 1999

run;

data table3 ;

  input pid date_2 ms $;

cards;

2 2002 A

3 2000 B

run;

data table4 ;

  input pid sex $ dob occ $ ms $ date ;

cards;

1 F 1985 EDU . 1999 

2 M 1986 IT A 1999

2 M 1986 IT A 2002

3 M 1986 EDU B 2000

run;

data dates;

  set table2 table3 ;

  by pid;

  drop ms;

run;

data want ;

  merge table1 table3 (keep=pid ms) dates ;

  by pid ;

  date = coalesce(date_1,date_2);

run;

proc compare data=want compare=table4;

run;

SASJedi
SAS Super FREQ

Nice!  For efficiency's sake (I know, I know - this data is small, but it's a personal obsession...) consider renaming the date variables during the data step concatenation to avoid executing the COALESCE function in the subsequent merge:

data dates;
  set table2 (rename=(date_1=date))
      table3 (rename=(date_2=date)
              drop=ms);
  by pid;
run;
 
data want ;
  merge table1 table3 (keep=pid ms) dates;
  by pid ;
run;

proc compare data=want compare=table4;
run;
Check out my Jedi SAS Tricks for SAS Users

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
  • 2 replies
  • 7857 views
  • 1 like
  • 3 in conversation