Here's the example!. I apologize for not providing detailed info also for late response.  I've just given only one example but there are two thousand records of different complaints.  Data A; Input complaint $100. ExactReason $50.; Cards; DAMAGED / MISSING PART WATER LINE REPLACED BROKEN WATER LINE ; RUN; DATA B; REASON $50.; CARDS; DOORS & HANDLES : DOOR / DRAWER APPEARANCE / FINISH DEFECT : MISSING / WRONG PARTS ; RUN; Output; Complaint ExactReason DAMAGED / MISSING PART WATER LINE REPLACED BROKEN WATER LINE DOORS & HANDLES : DOOR / DRAWER APPEARANCE / FINISH DEFECT : MISSING / WRONG PARTS Now, what I want is the value in Reason column of table B should be searched in the Complaint column of table A. If there's a match keep the value in Reason column in ExactReason column.  .      ... View more

By the way your SCAN is including SPACE as a delimiter. So If one of your values in delimited list includes a space that is counted as well. If you do not want to use a space as a delimiter do not include them in the third parameter   Instead of Code_C&i. = scan(Code&I., 12, ' : ') ; to not include space as delimiter Code_C&i. = scan(Code&I., 12, ':') ;  Data as an example is helpful because otherwise we have to make guesses as to what valid values may look like and quite often those guesses can be wrong. @Vasundha wrote: Hello Experts,  I'd be grateful if you could assist me on this.  I've a table with some columns named Code1-Code5. In these columns I've values separated with colon (:). So, for that I've written a macro .  Here's it is.  %macro abc; Data xyz; Set xyz; %do i =1 %to 5; Code_C&i. = scan(Code&I., 12, ' : ') ; %end; run; %mend abc; %abc In this code, the new variables are getting created but there are no values in it. Im having blank values in them. I've no clue why it isn't providing values the way it is supposed to. Please help me out on this.   ... View more

To avoid autofill of dates, use VLINE instead of SERIES. ... View more

Alright, Thanks!. ... View more

In my data I've newly added special characters so, for them the if condition mentioned in his code isn't working n also for a long string his code for d taking the last substring followed by /. For instance, in this long string it is considering only the last substring ie. attire1790&4&Y abcde1234&9&Y / defgh0987&2&N / attire1790&4&Y. ... View more

data have; input mass $80.; datalines; abcde1234&9&Y / defgh0987&2&N / attire1790&&4&Y Daikin1290&6&Y / rets0987&2&N / attire1790&4&Y ; data want; set have; count1=0;count2=0; do i=1 to countw(mass,'/'); temp=scan(scan(mass,i,'/'),1,'&','k'); if strip(temp)='&' then count1+1; if strip(temp)='&&' then count2+1; end; run; ... View more

Can't be thankful enough for this  ... View more

@Vasundha wrote: Need seperate variables for + and ++ i.e count1 and count2. Thankyou. We figured that out already. data want; set have; count1=count(tranwrd(string,'++',' '),'+'); count2=count(string,'++'); run; Obs string count1 count2 1 Abcdefghi+7/edfiklhopr+9 2 0 2 Jakdkopiqwe++9/ajsjsjajsj++10/ghakakak++12/hwjwjwnnw++1 0 4 3 George 0 0 4 Sam/Fred/x+y 1 0 ... View more

This is a macro solution: data xyz_01jan2022; set sashelp.class; run; data xyz_02jan2022; set sashelp.class; run; %let lib=WORK; /* set your library name here */ %macro combine; data want; set %do date = %sysevalf('01jan2022'd) %to %sysfunc(today()); %let dt = %sysfunc(putn(&date.,date9.)); %if %sysfunc(exist(&lib..xyz_&dt.)) %then %do; &lib..xyz_&dt. %end; %end; ; run; %mend; %combine Now compare that to the simplicity of using a wildcard (and add in the fact that YMD dates will always sort properly, so selecting files that are old enough for deleting/archiving or "new" enough for processing is done in a single comparison), and you see how bad the current design is. ... View more