Did you have code that worked before you started writing that macro?   If by "database" you mean a SAS Library then this would be my take. The example looks for any data set in the SASHELP library (which you should have) looking for a variable named Sex. Important to note: the case of the variable name in the Name variable can be of variable case. So I use UPCASE for a comparison to 'SEX', an equivalent lower case could be used. SAS will return a numeric 1/0 result for any logical comparison. So this selects the largest of all the comparisons of the names for each Memname. 1 indicates it was found, 0 not.   I strongly recommend using numeric 1 for "Yes" or "True" or "Present" and 0 for "No", "False","Absent" or similar dichotomous values. There things that just plain easier with the numeric values than the character.   proc sql; create table work.want as select memname,max(upcase(name)='SEX') as Variable_exists from dictionary.columns where LIBNAME='SASHELP' group by memname ; quit;   You would have to show an example of how you want your "multiple" , assuming you mean multiple variables, output data set to look. For example a minor change to my code would give a count of how many variables are in the set: proc sql; create table work.want as select memname,sum(upcase(name)in ('SEX' 'AGE') ) as Variable_exists from dictionary.columns where LIBNAME='SASHELP' group by memname ; quit; An example of that "numeric 1/0 is easier to work with" for many things.   ... View more

You could write a macro that reads the names of all the files in a folder (there are plenty of threads in the SAS Communities on how to do that), then if it is a .xlsx file, the macro imports it one way, if it is a .txt file then the macro imports it another way, and so on. ... View more

Sort data by customer name and date Use BY & Retain to store the first purchase date Use INTCK to calculate the difference between the sale date and the first purchase date Data temp; input cust_name $ sales_id $ sales_date date9. sales_amount; format sales_date date9.; cards; tom abc 07mar2019 120 tom def 25nov2019 155 tom ghi 02feb2020 110 tom jkl 07mar2020 165 tom mno 16apr2020 185 tom pqr 18oct2020 100 tom stu 05jan2021 55 tom vwx 19mar2021 67 john sdf 25jun2019 255 john vgh 05feb2020 122 john hui 15jul2020 333 john bhj 08sep2020 256 john njd 28jun2021 128 lee bhg 18oct2020 556 lee ggr 06Nov2020 546 lee njj 17oct2021 126 lee juy 20oct2021 512 lee aer 25oct2021 255 lee njk 18oct2022 596 ; run; proc sort data=temp; by cust_name sales_date; run; data want; set temp; by cust_name; retain first_purchase; if first.cust_name then first_purchase = sales_date; year_of_purchase = intck('year', first_purchase, sales_date, 'C') + 1; run; @_el_doredo wrote: Hello Experts, I have to rank my data based on their first purchase date. If a customer purchased on 05MARCH2020 means all the observation between 05MARCH2020 and 04MARCH2021 considered as 1(1st Year). Then all the observations between 05MARCH2021 and 04MARCH2022 considered as 2(2nd Year). We need this for each customer. As first purchase date will change for all customer. Please help me with this   This is my input data   Data temp; input cust_name $ sales_id $ sales_date date9. sales_amount; format sales_date date9.; cards; tom abc 07mar2019 120 tom def 25nov2019 155 tom ghi 02feb2020 110 tom jkl 07mar2020 165 tom mno 16apr2020 185 tom pqr 18oct2020 100 tom stu 05jan2021 55 tom vwx 19mar2021 67 john sdf 25jun2019 255 john vgh 05feb2020 122 john hui 15jul2020 333 john bhj 08sep2020 256 john njd 28jun2021 128 lee bhg 18oct2020 556 lee ggr 06Nov2020 546 lee njj 17oct2021 126 lee juy 20oct2021 512 lee aer 25oct2021 255 lee njk 18oct2022 596 ; run;   I want my output like this   cust_name sales_id sales_date sales_amount rank tom abc 07MAR2019 120 1 tom def 25NOV2019 155 1 tom ghi 02FEB2020 110 1 tom jkl 07MAR2020 165 2 tom mno 16APR2020 185 2 tom pqr 18OCT2020 100 2 tom stu 05JAN2021 55 2 tom vwx 19MAR2021 67 3 john sdf 25JUN2019 255 1 john vgh 05FEB2020 122 1 john hui 15JUL2020 333 2 john bhj 08SEP2020 256 2 john njd 28JUN2021 128 3 lee bhg 18OCT2020 556 1 lee ggr 06NOV2020 546 1 lee njj 17OCT2021 126 1 lee juy 20OCT2021 512 2 lee aer 25OCT2021 255 2 lee njk 18OCT2022 596 3   Thanks in Advance   ... View more