topic Re: How to combine tables? in SAS Programming

How to combine tables?

ybz12003 — Fri, 11 Aug 2017 11:14:16 GMT

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
;

Re: How to combine tables?

Kurt_Bremser — Fri, 11 Aug 2017 11:29:27 GMT

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.

Re: How to combine tables?

Astounding — Fri, 11 Aug 2017 12:00:39 GMT

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

Re: How to combine tables?

Kurt_Bremser — Fri, 11 Aug 2017 12:13:45 GMT

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.

Re: How to combine tables?

ybz12003 — Fri, 11 Aug 2017 19:09:12 GMT

Thanks for everyone's great suggetion.