There are limited SAS functions that can be used with %sysfunc, and there are also macro specific functions you can use without the %sysfunc.  I would suggest looking at the documentation that has been provided to determine which function you want and if it's already a macro function, or if you can use it inside a %sysfunc. ... View more

Then the macro code I provided above will get you that.  Below is the log from testing it.   When there was no work.data1 data set: 15         %macro dsexist(dsname=,dslib=); 16          17         %put &dslib..&dsname outputflag = %sysfunc(exist(&dslib..&dsname)); 18          19         %mend; 20          21         %dsexist(dsname=data1,dslib=work); data1.work outputflag = 0 22   When there was a work.data1 data set: 15         %macro dsexist(dsname=,dslib=); 16          17         %put &dslib..&dsname outputflag = %sysfunc(exist(&dslib..&dsname)); 18          19         %mend; 20          21         %dsexist(dsname=data1,dslib=work); work.data1 outputflag = 1 22 ... View more

Here's a way to display only the matches between sample_patients1 (eligible) and sample_patients2 (reference1) using the SQL procedure: proc sql; create table eligible_duplicates as select A.*, B.PRAMSFileNo as Dup_PRAMSFileNo from eligible A inner join reference1 B on (A.PRAMSFileNo=B.PRAMSFileNo); quit; ... View more

You can modified the code to include whatever variables you want.  I'm not sure exactly what you're looking to do with your outputflag and isexist macros though.  If you're just wanting to know if the data set exists and if it does or doesn't, display that in the log, you can do it by utilizing the exist() function as RW9 showed.  The value is either a 0 or 1 returned depending if the data set exists or not, so I don't think you would need to hardcode your outputflag and isexist values.  Below would be the macro modified with your variables if all you want to do is display in the log whether the data set exists or not: %macro dsexist(dsname=,dslib=); %put &dslib..&dsname outputflag = %sysfunc(exist(&dslib..&dsname)); %mend; %dsexist(dsname=data1,dslib=work); Log if data set doesn't exist:  "work.data1 outputflag = 0". Log if data set does exist:  "work.data1 outputflag = 1".   Is this what you're trying to achieve? ... View more

Here's a simple macro program to do what you want.  Just need to modify the code based on what you want to do if the data set exists, and what you want to do if the data set doesn't exist.  Hope this helps:   %macro check(dsn); %if %sysfunc(exist(&dsn)) %then %do; /***Insert code on what to do if data set exists***/ proc print data=&dsn; run; %end; %else %do; /***Insert code on what to do if data set doesn't exist***/ %put "Data set &dsn does not exist!"; %end; %mend; %check(datasetname); ... View more

Here's one way to do it.  I'm sure there may be a way to create only 1 data set using sub queries and such, but this should get you what you're looking for. data dataset1; input ID$ date_var1; informat ID $2. date_var1 mmddyy10.; format ID $2. date_var1 mmddyy10.; datalines; 1 2/5/2013 2 9/13/2014 3 6/4/2012 ; run; data dataset2; input ID$ date_var2 categ_var2$; informat ID $2. date_var2 mmddyy10. categ_var2 $3.; format ID $2. date_var2 mmddyy10. categ_var2 $3.; datalines; 1 1/4/2013 abc 1 1/10/2013 def 1 2/1/2014 ghi 3 8/17/2012 abc 3 9/12/2013 jkl ; run; proc sql; create table date as select A.ID, max(A.date_var2) as date_var2 format=mmddyy10. from dataset2 A, dataset1 B where (A.ID=B.ID AND A.date_var2 < B.date_var1) group by A.ID; create table new_dataset2 as select A.* from dataset2 A, date B where (A.ID=B.ID AND A.date_var2=B.date_var2); create table want as select A.*, B.date_var2, B.categ_var2 from dataset1 A left join new_dataset2 B on (A.ID=B.ID); quit; ... View more

Here's another way to do what you're looking for.  It's not dynamic, but you could probably develop some additional code to make it dynamic if needed (as far as additional variables and methods other than 2 each).  Hope this helps!   data have; input Person$ Location$ Method$ Var1 Var2; datalines; Person1 X 1 1 3 Person1 Y 1 5 7 Person1 X 2 2 4 Person1 Y 2 6 8 Person2 X 1 9 11 Person2 Y 1 13 15 Person2 Z 1 17 19 Person2 X 2 10 12 Person2 Y 2 14 16 Person2 Z 2 18 20 ; run; %macro transform(method,var); data &var._&method (Rename=(&var=&var._Method&method) Keep=Person Location &var); set have; Where Method="&method"; run; %mend; %transform(1,Var1) %transform(2,Var1) %transform(1,Var2) %transform(2,Var2); data want; merge Var1_1 Var1_2 Var2_1 Var2_2; by Person Location; run; ... View more

As RW9 previously mentioned, it is important to understand the different types of joins with the SQL procedure.  From what I understand with your examples (and you may want to post some example data from both data sets to help us understand), but I believe the IPO data set looks to have duplicate values based on the variable Date I, and the Calendar data set seems to have one record per Date L variable.  If this is the case and you want to keep all of your records from the IPO data set, I would suggest using IPO as the left data set and doing a left join, as this will keep every record from the IPO data set and bring in values from the Calendar data set where the two dates are matching.  The venn diagrams RW9 provided below are a great visual of how the different joins work.   The other thing I noticed were the names of your date variables.  If they have spaces in the names from a import procedure, you will need to inclose them between double quotes and the character n at the end.  For example Date I would be "Date I"n, otherwise you will get syntax errors.  You also need to specify the alias for each data set in your FROM statement as you are using an alias in front of the date variables, but never declared them.  Below would provide you every variable from both data sets (you'll get a warning message if you have two or more of the same variable names in each data set, and it will drop one of them); as well as, keep every record from your IPO data set and provide values for the Calendar variables where the Date L variable matches the Date I variable.   Hope this helps!   proc sql; create table merge1 as select * from Ipo as Ipo left join Calendar as Calendar where Ipo."Date I"n=Calendar."Date L"n order by Ipo."Date I"n; quit; ... View more

You can use a DROP statement, either in parenthesis during the DATA statement, or after the SET statement, not in parenthesis.   data want (DROP=variable); set have; run; data want; set have; DROP variable; run; ... View more