topic Re: Create macro for to cross check the uniqueness of employee id in SAS Programming

Create macro for to cross check the uniqueness of employee id

venkatnaveen — Sun, 24 Aug 2014 17:45:47 GMT

There is a list of employee ID and every month a new employee is added. Write a Macro program to cross check the uniqueness of the employee ID.

Re: Create macro for to cross check the uniqueness of employee id

Cynthia_sas — Sun, 24 Aug 2014 18:01:02 GMT

Hi:

You do not need a macro program to do this. Simply create a temporary file for the new month and then use PROC FREQ with nlevels. Any EmpID with nlevel=2 is a duplicate. Or use PROC SORT with the DUPOUT option. Or define an index for Employee ID in the main/master file to have the UNIQUE property and you will not be able to add duplicate employee IDs to the master file. This sounds like a homework problem. What code have you tried? Even if you do ultimately want a macro program, you have to start with a working SAS program before you "macro-ize" it. So, show your work. What does the input data look like for the "master" file? What does the data look like for the monthly add? What is the working program to add the new observation(s) to the master file?

cynthia

Re: Create macro for to cross check the uniqueness of employee id

venkatnaveen — Sun, 24 Aug 2014 18:05:35 GMT

Thanks cynthia .

Its a interview question asked.

Re: Create macro for to cross check the uniqueness of employee id

SASKiwi — Sun, 24 Aug 2014 20:09:07 GMT

As a general rule a macro is only useful if it is to be repeated multiple times in your SAS jobs.

Therefore it might be a good idea to have general "check for duplicates" macro that could be applied to any dataset with a key variable and not just employee ID. Here is an example and this is one way of answering your interviewer

%macro Find_Dups ( dataset =

,byvar =

,dupvar =

);

%if &dupvar = %then %let dupvar = &byvar;

proc sort data = &dataset

out = sorted

;

by &byvar;

run;

data dups;

set sorted;

by &byvar;

if not (first.&dupvar and last.&dupvar);

run;

%mend Find_Dups;

%Find_Dups( dataset = MyData

,byvar = EmployeeID);

Re: Create macro for to cross check the uniqueness of employee id

venkatnaveen — Mon, 25 Aug 2014 07:56:36 GMT

Thanks a ton.

Good explanation.