I tested it, you do get errors but not regarding the macro not found. The issue is the variable ID is in multiple datasets but has different types (char vs numeric) so you can't append then. Your method will have the same issue, unless your data is clean.   You also realize the code is appending the datasets with all variables not just the variables you've listed? You may want to add a KEEP statement to the data step.    54 %macro sap(lib=,varlist=); 55 56 proc sql noprint; 57 select distinct catx(".",libname,memname) into : datalist separated by " " 58 from dictionary.columns 59 where 60 libname = 'SASHELP' 61 and findw("&varlist",strip(name),' ','i')>0 62 and memtype='DATA' 63 ; 64 quit; 65 66 data test1; 67 set &datalist; 68 run; 69 70 proc print data=work.test1 (obs=20); 71 run; 72 73 %mend; 74 75 76 %sap(lib=sashelp,varlist=name height); NOTE: PROCEDURE SQL used (Total process time): real time 0.04 seconds cpu time 0.04 seconds WARNING: Multiple lengths were specified for the variable Name by input data set(s). This can cause truncation of data. WARNING: Multiple lengths were specified for the variable Sex by input data set(s). This can cause truncation of data. WARNING: Multiple lengths were specified for the variable type by input data set(s). This can cause truncation of data. WARNING: Multiple lengths were specified for the variable subtype by input data set(s). This can cause truncation of data. WARNING: Multiple lengths were specified for the variable desc by input data set(s). This can cause truncation of data. NOTE: There were 322 observations read from the data set SASHELP.BASEBALL. NOTE: There were 19 observations read from the data set SASHELP.CLASS. NOTE: There were 19 observations read from the data set SASHELP.CLASSFIT. NOTE: There were 4 observations read from the data set SASHELP.FEEDER. NOTE: There were 159 observations read from the data set SASHELP.FISH. NOTE: There were 3600 observations read from the data set SASHELP.GRIDDED. NOTE: There were 5209 observations read from the data set SASHELP.HEART. NOTE: There were 27 observations read from the data set SASHELP.HOLIDAY. NOTE: There were 1703 observations read from the data set SASHELP.SLKWXL. NOTE: There were 2373 observations read from the data set SASHELP.SVRTDIST. NOTE: There were 11 observations read from the data set SASHELP.VBPLAYRS. NOTE: There were 44 observations read from the data set SASHELP._CMPIDX_. NOTE: The data set WORK.TEST1 has 13490 observations and 87 variables. NOTE: DATA statement used (Total process time): real time 0.09 seconds cpu time 0.09 seconds ... View more

@RTelang wrote: @kurtBrememer this I tried but i fail to understand how do i take location and check data-sets at that location see if the column exist then assign random values... %macro val(lib=,ds=,libname=,column=); libname myfile "&lib"; %if %sysfunc(exist(myfile.&ds))=0 %then %do; %if %sysfunc(exist(myfile.&ds,view))=0 %then %do; proc sql; create table datasets as select distinct memname from dictionary.columns where upcase(libname) = upcase("&lib") and upcase(name) = upcase("&column"); quit; data _null_; set datasets end=done; if _n_ = 1 then call execute('data values;set '); call execute("&lib.."!!strip(memname)!!' '); if done then call execute(": keep &column; run;"); run; proc sort data=values nodupkey; by &column; run; %end; %end; %quit: %mend val; %val(lib='C:\Users\Desktop\Myfiles',ds=test1,column=name); but gives error Invalid physical name for library MYFILE. ERROR: Error in the LIBNAME statement . File WORK.VALUES.DATA does not exist.   I hate to repeat myself, but:   Before putting it into a macro, do it step-by-step   !!! ... View more

And a do statement must be terminated with a semicolon.   It is always helpful to run the code that shall go into a macro without the macro definition first, to check that you get correct SAS code with the supplied parameters: DATA COUNTRY; input country $25.; CARDS; INDIA INDIA INDIA INDIA SPAIN SPAIN SPAIN SPAIN GERMANY GERMANY GERMANY GERMANY ; run; *%macro Country_Check (indsn=, variable=, outdsn=,freq=); %let indsn=country; %let variable=country; %let outdsn=want1; %let freq=4; PROC SORT DATA=&indsn; by &variable; run; PROC FREQ DATA=&indsn; TABLES &variable / noprint out=counts (KEEP=&variable count); run; proc format; value $city 'INDIA'='ASIA' 'GERMANY'='EUROPE' 'SPAIN'='EUROPE' 'BELGIUM'='EUROPE' 'FRANCE'='EUROPE' other='UNKNOWN'; run; data &outdsn; MERGE &indsn counts; By &variable; if count <= &Freq then do; newvar= put(&variable, $city.); end; DROP count; run; proc print data = &outdsn; run; *%MEND Country_Check; *%Country_Check (indsn=myfile.COUNTRY, variable=country, outdsn=want1, Freq=4); This will work. You can proceed from there.     ... View more

In case you want to use a macro like a function, you need to define it differently. But I'd rather use a user-defined data step function instead: %macro extract_letter(instring); substr(scan(&instring,-2,'\'),1,1) %mend; proc fcmp outlib=work.funcs.test; function extract_letter(instring $) $; return (substr(scan(instring,-2,'\'),1,1)); endsub; run; options cmplib=work.funcs; data want; fullpath = 'D:\Projects\SDTM_Mapping\domains\BD2 - \D - \SDTM-v3.xlsx '; result1 = extract_letter(fullpath); result2 = extract_letter('D:\Projects\SDTM_Mapping\domains\BD2 - \D - \SDTM-v3.xlsx '); result3 = %extract_letter(fullpath); result4 = %extract_letter('D:\Projects\SDTM_Mapping\domains\BD2 - \D - \SDTM-v3.xlsx '); run; Note that the results in the dataset from the function will have the desired length of 1, while the results from the macro invocation will default to length 200. ... View more