topic Merging 2 datasets with different number of observations in SAS Procedures

Merging 2 datasets with different number of observations

PCrider — Thu, 17 Mar 2011 06:39:53 GMT

I have a dataset concerning logins and logouts on a computer. The problem with the dataset is that there is not an equal amount of logins and logouts. (i.e. one person logged in and never logged out, or a different person logged out twice and never had a login, etc)

I have separated the set into 2 datasets, one for logins, one for logouts. Now I want to put the two back together grouping by each person, but adding a new observation if a login or a logout is missing that includes all the other variables.

ex:

OUT joe34 comp1 Mon. 2:25:45
OUT bob56 comp3 Mon. 3:45:24
IN dav87 comp2 Tues. 7:13:32
OUT dav87 comp2 Tues. 7:25:55
OUT sam78 comp2 Fri. 1:36:11
IN jak22 comp5 Sat. 12:24:32

So, I want each person to have 2 observations, one with IN etc... and the next with OUT etc...

Any ideas?

Re: Merging 2 datasets with different number of observations

Ksharp — Thu, 17 Mar 2011 08:00:35 GMT

OK. That is not too complicated!
[pre]

data temp;
input log $ name $ comp $ dt & $20.;
datalines;
OUT joe34 comp1 Mon. 2:25:45
OUT bob56 comp3 Mon. 3:45:24
IN dav87 comp2 Tues. 7:13:32
OUT dav87 comp2 Tues. 7:25:55
OUT sam78 comp2 Fri. 1:36:11
IN jak22 comp5 Sat. 12:24:32
;
run;
[/pre]
proc sql;

create table _temp as

select *

from (select distinct
log from temp),(select distinct

name from temp)

order by log,name;

quit;

proc sort data=temp;

by log name;

run;

data _result;

merge temp _temp;

by log name;

run;

proc sort data=_result;

by name;

run;

data result;

merge _result(keep=log name)

_result(keep=name comp dt

where=(comp is not missing or dt is not missing));

by name;

run;
[pre]

[/pre]

Ksharp