Macro to combine datasets

[Moksha]

Hi All,

      I need help to combine two datasets using macro which takes datasets and variables as parameters. I need to check if the datasets & variables exist or not and then combine both the datasets. Can anyone help me with some example code?

       I am new to SAS.

Thanks

[PaigeMiller]

What do you mean by "combine"?

Show us working code that does what you want for just two datasets, with no macros and no macro variables.

[Moksha]

I mean merge.

For example, I have the following code (without macros) for merging CustAddress and orders datasets by customernumber and I also have a macro code for checking if the passed in dataset has data in it or not. 

I want to know how to to write a macro in which I can pass CustAddress and orders datasets and the by variable customernumber. The macro should check if the passed in datasets exist and if exist check if they have data in them. If both datasets and data exist in them then check if the variable customernumber that is passed exists in both the sets or not. If exists then merge the datasets to achieve the functionality shown the code without macros.

Code without macros:

data CustAddress;
input customerNumber Name $ 5-21 Address $ 23-42;
datalines;
101 Murphy's Sports 115 Main St.
102 Sun N Ski 2106 Newberry Ave.
103 Sports Outfitters 19 Cary Way
104 Cramer & Johnson 4106 Arlington Blvd.
105 Sports Savers 2708 Broadway
;
run;
proc print data=CustAddress;
run;

data orders;
input CustomerNumber Total;
datalines;
102 562.01
104 254.98
104 1642.00
101 3497.56
102 385.30
;
run;

proc print data=orders;
run;

proc sort data=orders;
by CustomerNumber;

proc print data=orders;
title 'customer orders';
run;
*Combine the data sets using IN option;
data noorders;
merge CustAddress orders(in=order);
by customernumber;
if order=0;
run;
proc print data=noorders;
title 'customer''s with no orders in third quarter';
run;

Following is the macro code:

/*Open the data set of interest*/

%let dsnid = %sysfunc(open(&dsn));

%*If the open was successful get the number of observations and close &dsn;

%if &dsnid %then %do;

%let nobs = %sysfunc(attrn(&dsnid,nobs));

%let rc = %sysfunc(close(&dsnid));

%end;

%else %do;

%put Unable to open &dsn -;

%put %sysfunc(sysmsg());

%end;

%if &nobs %then %do;

    %*Return the number of observations;

&nobs;

%end;

%else %do;

/* %put %sysfunc(sysmsg());*/

%put ERROR - Empty dataset;

%end;

%mend obscnt;

[PaigeMiller · Accepted solution]

options mprint;
%macro dothis(dsn1=,dsn2=,id=); 
	/* First check if data sets exist */
	%if %sysfunc(exist(&dsn1)) and %sysfunc(exist(&dsn2)) %then %do;
            /* Next check to see if each data set has variable named &id */
		proc sql noprint;
			select count(name) into :ds1_name from dictionary.columns
			    where libname='WORK' and upcase(memname)="%upcase(&dsn1)" and upcase(name)="%upcase(&id)";
			select count(name) into :ds2_name from dictionary.columns
			    where libname='WORK' and upcase(memname)="%upcase(&dsn2)" and upcase(name)="%upcase(&id)";
		quit;
                /* If &id exists in both data sets */
		%if &ds1_name and &ds2_name %then %do;
			proc sort data=&dsn1;
			    by &id;
		    run;
			proc sort data=&dsn2;
			    by &id;
		    run;
			data want;
			    merge &dsn1 &dsn2(in=order);
			    by &id;
			    if order=0;
		    run;
		%end;
		%else %put Variable named &id does not exist in one or both data sets;
	%end;
	%else %put At least one data set does not exist;
%mend;
%dothis(dsn1=custaddress,dsn2=orders,id=customernumber)

[Moksha]

Thank you very much PaigeMiller. From this I could learn how to do the merge using a macro. It helped me a lot.

[Kurt_Bremser]

"New to SAS" and "macro" don't go together well. You need to first build your knowledge of the non-macro SAS elements before you can (successfully) engage in macro programming.

Think of three quite intensive course weeks (as was the case for me) over the course of a year (so you get to apply what you learned in "real life") before you attend your first macro course.

[ballardw]

---

@Moksha wrote:

Hi All,

      I need help to combine two datasets using macro which takes datasets and variables as parameters. I need to check if the datasets & variables exist or not and then combine both the datasets. Can anyone help me with some example code?

       I am new to SAS.

Thanks

---

With a description that vague you may need to add "check on the variable properties like type and length" as well. Depending on your particular definition of combine you may not be able to with variables of the same name if they have different types. Or you may lose data if the lengths vary. Since Dates, Times and Datetimes are numeric you may also have to check what the assigned format for variables that should be dates or times because the units between dates and time/datetime values changes from Days for date to Seconds of the other. So if the variables have different actual values there can be entertaining results. Typically either your "dates" are in 1960 or possibly after the year 5000.

Do you know what you want to do if the variables of the same name have different LABELS?

I worked with data I had to combine to build a multyear data set. The variable Q5 in some years referred to cigarette smoking, in other years a diagnosis of high blood pressure.

Then there were the cases where the meaning of the response codes to some of the questions changed.

So you may have more to descibe.