topic how to combine 2 dataset by common variable with out replacing missing values for other variables in SAS Procedures

how to combine 2 dataset by common variable with out replacing missing values for other variables

mrug12 — Tue, 25 Oct 2011 17:09:20 GMT

Here for example,

one dataset is

serial no start date

101 jan2008

101 feb2008

101 march 2008

102 feb 2007

102 march2007

102 april2007

secon dataset is

serial no end date

101 july2007

101 aug2007

101 sept2007

102 may 2008

102 june 2008

102 july2008

How can I get dataset like bellow from above 2 datasets by using common variable serial no

serial no start date end date

101 . july2007

101 . aug2007

101 . sept2007

101 jan2008 .

101 feb 2008 .

101 march .

102 feb2007 .

102 mar2007 .

102 april2007 .

102 . may2008

102 . june2008

102 . july2008

how to combine 2 dataset by common variable with out replacing missing values for other variables

art297 — Tue, 25 Oct 2011 17:24:06 GMT

You didn't explain what missing data you don't want to modify, but I think you either want to use the UPDATE statement in a datastep or write your own rules using a MERGE of the files in a datastep. The update statement is described at: http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a001329151.htm

how to combine 2 dataset by common variable with out replacing missing values for other variables

mrug12 — Tue, 25 Oct 2011 17:33:06 GMT

As in result dataset i do not want to modify missing data for start date and end date values as i want to keep both as per timeline and if value do not exit for that perticular month for any of them I want to keep it same way

how to combine 2 dataset by common variable with out replacing missing values for other variables

Reeza — Tue, 25 Oct 2011 17:35:48 GMT

From the want data set it looks like you want a set statement rather than a merge.

Does the following do what you want?

data want;

set table1 table2;

run;

If you need to keep the missing in their position you may need to create another variable within serial no that allows you to sort by that.

HTH,

Reeza

how to combine 2 dataset by common variable with out replacing missing values for other variables

mrug12 — Tue, 25 Oct 2011 17:50:33 GMT

As i mention i am creating dataset from 2 dataset using serial no as common variable and only values common in both dataset based on serial no will be kept .

so i can not use set satement .

how to combine 2 dataset by common variable with out replacing missing values for other variables

art297 — Tue, 25 Oct 2011 17:57:13 GMT

It sounds like you simply want to do a merge of the two datasets. You do that either in a data step or, in proc sql, with a full join.

how to combine 2 dataset by common variable with out replacing missing values for other variables

Reeza — Tue, 25 Oct 2011 18:16:41 GMT

Do the periods under the serial no indicate missing serial no or a continuation of the previous serial no. Because otherwise all missing serial no's would be included according to your critieria.

Given that you're not overwriting or actually adding in a column it really sounds like you need an append or a set statement with a condition that the dataset contains only records in both files. This is different than a merge in my opinion.

You can try sql something like the following (untested code)

proc sql;

create table want as

select * from (select * from table1 where serial_no in (select serial_no from table2)

union all

select * from table2 where serial_no in (select serial_no from table1)) a;

quit;

If you actually have serial no missing that you need they may cause problems as I mentioned above and you'll have to identify them first somehow.

how to combine 2 dataset by common variable with out replacing missing values for other variables

mrug12 — Tue, 25 Oct 2011 18:53:37 GMT

here period indicates continuation,,,,not missing serial no,,,,

and i think concept is right but ,,,,union all can not be use as columns in both data set is not same ,,,

and not able to get result through it,,

how to combine 2 dataset by common variable with out replacing missing values for other variables

art297 — Tue, 25 Oct 2011 18:57:40 GMT

Do you have duplicate serial number, enddate combinations in either dataset?

how to combine 2 dataset by common variable with out replacing missing values for other variables

mrug12 — Tue, 25 Oct 2011 18:58:00 GMT

here i want to merge 2 dataset but with common varibale with matching values record only,,,,,and during this combination ,,I do not want to apply values to missing values for compared columns and want to retain values and apply missing value where value is not available.

how to combine 2 dataset by common variable with out replacing missing values for other variables

mrug12 — Tue, 25 Oct 2011 19:00:17 GMT

yes serial no value is repeated sevral time as per ex. tables but enddate value is different for each one

how to combine 2 dataset by common variable with out replacing missing values for other variables

Reeza — Tue, 25 Oct 2011 19:11:59 GMT

What about two steps then.

data want1;

set table1 (in=a) table2(in=b);

if a then source='a';

else if b then source='b';

run;

proc sql;

createt able want2

select * from want1

group by serial_no

where max(source) ne min(source); *check for both ids in there not just 1;

quit;

Again untested, but closer I think.

how to combine 2 dataset by common variable with out replacing missing values for other variables

art297 — Tue, 25 Oct 2011 19:12:50 GMT

Does the following example match what you want to accomplish?:

data one;

informat start_date date9.;

format start_date date9.;

input serial_no start_date;

cards;

101 1jan2008

101 1feb2008

101 1mar2008

102 1feb2007

102 1mar2007

102 1apr2007

;

data two;

informat end_date date9.;

format end_date date9.;

input serial_no end_date;

cards;

101 1jul2007

101 1aug2007

101 1sep2007

102 1feb2007

102 1may2008

102 1jun2008

102 1jul2008

;

data twoprep;

set two;

start_date=end_date;

run;

data want;

merge one (in=a) twoprep (in=b);

by serial_no start_date;

if b and not(a) then

call missing(start_date);

run;

how to combine 2 dataset by common variable with out replacing missing values for other variables

mrug12 — Tue, 25 Oct 2011 19:53:08 GMT

REEZA,

it did not work as it was using where and it not valid data step,,,,as per log error

how to combine 2 dataset by common variable with out replacing missing values for other variables

mrug12 — Tue, 25 Oct 2011 20:00:47 GMT

in end i do not able to get value for end date seprately or so,,,,

how to combine 2 dataset by common variable with out replacing missing values for other variables

art297 — Tue, 25 Oct 2011 20:15:54 GMT

You need to explain. The code and example I sent would provide values for start and/or enddates if either or both exist in your file.

how to combine 2 dataset by common variable with out replacing missing values for other variables

Reeza — Tue, 25 Oct 2011 20:32:18 GMT

Show the code you used and the log error...

how to combine 2 dataset by common variable with out replacing missing values for other variables

mrug12 — Tue, 25 Oct 2011 20:37:54 GMT

THANKS FOR THE HELP BUT LOOK LIKE THE DATASET TO BEGEN WITH IS CURREPT AND I NEED TO WORK ON THAT DETAILS FIRST TO START WITH ,,,,,AS SO SORRY FOR TROUBLE,,,,,,AND THANKS FOR THE REPLY AND SUGGESTION,,,,

Re: how to combine 2 dataset by common variable with out replacing missing values for other variables

Tom — Tue, 25 Oct 2011 22:28:38 GMT

The basic idea of interleaving records from two (or more) dataset in ID order can by done with SET and BY statements.

Did you want to more than that?

data one;
  input serial start $15. ;
cards;
101 jan2008
101 feb2008
101 march2008
102 feb2007
102 march2007
102 april2007
run;
data two ;
  input serial end $15.;
101 july2007
101 aug2007
101 sept2007
102 may2008
102 june2008
102 july2008
run;
data both ;
  set one two;
  by serial;
run;

Output:

Obs serial start end

1 101 jan2008

2 101 feb2008

3 101 march2008

4 101 july2007

5 101 aug2007

6 101 sept2007

7 102 feb2007

8 102 march2007

9 102 april2007

10 102 may2008

11 102 june2008

12 102 july200

Re: how to combine 2 dataset by common variable with out replacing missing values for other variables

Tom — Tue, 25 Oct 2011 22:42:50 GMT

Now if you want to also order by the date then you will need to first give it real dates and then copy the start and end dates to another variable that you can use to order.

data one;
  format serial 5. date start date9.;
  input serial start date9. ;
  date=start;
cards;
101 01jan2008
101 01feb2008
101 01mar2008
102 01feb2007
102 01mar2007
102 01apr2007
run;
data two ;
  format serial 5. date end date9.;
  input serial end date9.;
  date=end;
cards;
101 01jul2007
101 01aug2007
101 01sep2007
102 01may2008
102 01jun2008
102 01jul2008
run;
data both ;
  set one two;
  by serial date ;
run;
options nocenter nodate nonumber linesize=50;
proc print width=min;
run;

Obs serial date start end

1 101 01JUL2007 . 01JUL2007

2 101 01AUG2007 . 01AUG2007

3 101 01SEP2007 . 01SEP2007

4 101 01JAN2008 01JAN2008 .

5 101 01FEB2008 01FEB2008 .

6 101 01MAR2008 01MAR2008 .

7 102 01FEB2007 01FEB2007 .

8 102 01MAR2007 01MAR2007 .

9 102 01APR2007 01APR2007 .

10 102 01MAY2008 . 01MAY2008

11 102 01JUN2008 . 01JUN2008

12 102 01JUL2008 . 01JUL2008