topic Re: Merge in SAS Programming

Merge

souji — Fri, 10 Jan 2020 21:32:32 GMT

Please let me know how many observations are in work.Merge dataset,I am assuming to, but I got only one observation.

please help me...Thank you

data work.EMP_NAME;

input Name $4. EmpID 5. ;

datalines;

Jill 1864

Jack 2121

Joan 4698

John 5463

;

run;

data work.EMP_DEPT;

input EmpID 5. Department $5.;

datalines;

2121 Acct

3567 Fin

4698 Mktg

5463 Acct

;

run;

proc sort data=work.EMP_NAME;

by EmpID;

run;

Proc sort data=work.EMP_DEPT;

by EmpID;

run;

　data work.Merge;

merge work.EMP_NAME (in=EMP_N)

work.EMP_DEPT (in=EMP_D);

by EmpID;

if (EMP_N and not EMP_D);

run;

Re: Merge

Astounding — Fri, 10 Jan 2020 22:12:14 GMT

The results you are getting look like they are correct.

Which two observations do you think you should be getting?

Re: Merge

unison — Fri, 10 Jan 2020 23:42:26 GMT

You will only have Jill as she is the only one with an employee number (in EMP_N) without a department (not in EMP_D).

As a side note, refrain from using merge as a dataset name -- this a reserved word in SAS. Change to work.merged_data or something like that.

Cheers

Re: Merge

Tom — Sat, 11 Jan 2020 00:16:05 GMT

To see what is happening save your IN= flags and check how they are set for each observation.

data Merge;
  merge EMP_NAME (in=EMP_N)
        EMP_DEPT (in=EMP_D)
  ;
  by EmpID;
  flags=cats(emp_n,emp_d);
run;

proc freq ;
  tables flags;
run;

The FREQ Procedure

                                  Cumulative    Cumulative
flags    Frequency     Percent     Frequency      Percent
----------------------------------------------------------
01              1        8.33             1         8.33
10              1        8.33             2        16.67
11             10       83.33            12       100.00

To get 2 outputs your condition would need to be:

not (emp_d and emp_n)

Note it is impossible for FLAGS to be '00'. Do you see why?