How to combine tables?

Accepted Solution Solved
Reply
Super Contributor
Posts: 297
Accepted Solution

How to combine tables?

Hello:

 

Firest, I would like to combine the column lab_mom in data1 and the column lab_child in data2 in one column lab.  Second, I would like to assign M and C in the column Seq.  Last, could someone let me know why my data format is not right?  I could not get mm/dd/yyyy in the dataset.   Please advise how to do it.  Thanks.

 

data data1;
informat ID $4. Seq best8. DOB mmddyy10. lab_mom $8. ;
input ID Seq DOB lab_mom;
cards;
A045 1 05/06/1979 NH
A045 2 05/06/1979 NH
B100 1 06/15/1982 JJ
B100 2 06/15/1982 JJ
C300 1 05/06/1979 KK
C300 2 05/06/1979 KK
;

data data2;
informat ID $4. Seq best8. DOB mmddyy10. lab_child $8. ;
input ID Seq DOB lab_child;
cards;
A045 1 05/06/1979 MJ
B100 1 06/15/1982 PL
C300 1 05/06/1979 OL
;

data combine;
informat ID $4. Seq best8. DOB mmddyy10. lab $8. ;
input ID Seq DOB lab;
cards;
A045 1M 05/06/1979 NH
A045 2M 05/06/1979 NH
A045 1C 05/06/1979 MJ
B100 1M 06/15/1982 JJ
B100 2M 06/15/1982 JJ
B100 1C 06/15/1982 PL
C300 1M 05/06/1979 KK
C300 2M 05/06/1979 KK
C300 1C 05/06/1979 OL
;


Accepted Solutions
Solution
Friday
Super User
Posts: 6,928

Re: How to combine tables?

[ Edited ]

You can't attach a character to seq, as it is a numeric variable. So you have to create a new variable of type character.

Do a

data combine;
set
  data1 (in=in1 rename=(lab_mom=lab))
  data2 (in=in2 rename=(lab_child=lab))
;
by ID;
length newseq $2 lab $8;
if in1 then newseq = put(seq,1.) !! 'M';
if in2 then newseq = put(seq,1.) !! 'C';
run;

 Edit: added the rename= dataset options.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
Friday
Super User
Posts: 6,928

Re: How to combine tables?

[ Edited ]

You can't attach a character to seq, as it is a numeric variable. So you have to create a new variable of type character.

Do a

data combine;
set
  data1 (in=in1 rename=(lab_mom=lab))
  data2 (in=in2 rename=(lab_child=lab))
;
by ID;
length newseq $2 lab $8;
if in1 then newseq = put(seq,1.) !! 'M';
if in2 then newseq = put(seq,1.) !! 'C';
run;

 Edit: added the rename= dataset options.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,071

Re: How to combine tables?

Regarding the other half of your question ... reading in dates ... you are doing it properly.  It looks like the results are wrong, because the dates now contain a five-digit number.  To view the dates in a way that looks like a date, you have to assign a format.  Any of these are possible (as well as many more):

 

format dob date9.;

format dob yymmdd10.;

format dob mmddyys10.;

 

You might want to read a bit of the documentation on how SAS handles dates:

 

https://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a002200738.htm

 

Super User
Posts: 6,928

Re: How to combine tables?

Ups, missed the format question. In addition to what @Astounding already said: assigning an informat does not automatically set a matching display format, this must be done in a separate statement.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 297

Re: How to combine tables?

Thanks for everyone's great suggetion.

 

 

 

 

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 137 views
  • 2 likes
  • 3 in conversation