@Alexxxxxxx  Is this what you're looking for? data table1; infile cards truncover; input old $50. ; cards; 00163T10 755F10 00846U12560 1555 ;;;; data want; set table1; new = strip(old); if length(new) < 9 then do; do until (length(new) = 9); new = '0'||new; end; end; run; ... View more

@Barkat wrote: Is there any way to use do loop for the following. Because some of the city names have about 50 incorrect names.  Yes, here are two modified versions using loops:   1. If the columns are always INCORRECT_NAME1 to INCORRECT_NAME50: %macro loop_incorrect_names; proc sql; create table want as select a.name ,a.id ,a.date format MMDDYY10. ,case when b.correct_name is null then a.city_name else b.correct_name end as city_name from have1 a left join have2 b on (a.city_name = b.incorrect_name1 %do i = 1 %to 50; /* If the columns are always INCORRECT_NAME1 to INCORRECT_NAME50 */ or a.city_name = b.incorrect_name&i %end;) ; quit; %mend; %loop_incorrect_names 2. Or if there are not always 50 INCORRECT_NAME% columns, this will go up to the last (i.e. rightmost) INCORRECT_NAME column. (Note: I am assuming HAVE2 contains only the CORRECT_NAME column, followed by all the INCORRECT_NAME columns, ordered and named per your example.) proc contents data=have2 out=meta (keep=NAME) noprint; run; proc sql noprint; select substr(name, 15) into: max_incorrect_name_number from meta having monotonic()=max(monotonic()); quit; %put &=max_incorrect_name_number; %macro loop_incorrect_names; proc sql; create table want as select a.name ,a.id ,a.date format MMDDYY10. ,case when b.correct_name is null then a.city_name else b.correct_name end as city_name from have1 a left join have2 b on (a.city_name = b.incorrect_name1 %do i = 1 %to &max_incorrect_name_number.; /* Using the last INCORRECT_NAME column number */ or a.city_name = b.incorrect_name&i %end;) ; quit; %mend; %loop_incorrect_names     ... View more

This should give what you're looking for: data have1; infile datalines dsd dlm=',' ; input name :$6. id :$3. date :MMDDYY10. city_name :$12. ; datalines; Max,010,01/10/2017,Almeda Robin,023,.,Sacramanto Sandra,125,08/10/2013,LA Bob,200,01/12/2016,Woodland John,202,05/30/2014,L.A. ; run; data have2; infile datalines dsd dlm=',' ; input correct_name :$15. incorrect_name1 :$15. incorrect_name2 :$15. incorrect_name3 :$15. incorrect_name4 :$15. incorrect_name5 :$15. ; datalines; Sacramento, Sacraminto, Sacrament, Sacramintu, Scraminto, Sacramanto Los Angeles,Loss Angeles,LA,LAngeles,L.A.,- Woodland,Woodland,Woodland,-,-,- Richmond,Rchmond,Richmand,Richmon,-,- Alameda,Almeda,-,-,-,- ; run; proc sql; create table want as select a.name ,a.id ,a.date format MMDDYY10. ,case when b.correct_name is null then a.city_name else b.correct_name end as city_name from have1 a left join have2 b on (a.city_name = b.incorrect_name1 or a.city_name = b.incorrect_name2 or a.city_name = b.incorrect_name3 or a.city_name = b.incorrect_name4 or a.city_name = b.incorrect_name5) ; quit;   ... View more

proc sql; delete from my_dataset where (Loc = 'A' and Hyb = 'X03' and Ntrt = 'High' and Density = 'Low') or (Loc = 'B' and Hyb = 'X04' and Ntrt = 'Low' and Density = 'Low'); quit; Or, if you just want to exclude those rows: proc sql; create table want as select * from my_dataset where not (Loc = 'A' and Hyb = 'X03' and Ntrt = 'High' and Density = 'Low') and not (Loc = 'B' and Hyb = 'X04' and Ntrt = 'Low' and Density = 'Low'); quit;   ... View more

Is this somewhat in the direction you're hoping? The code below uses PRXMATCH to see if each string contains 'homeless' and 'no' within 10 characters of each other. You can modify the gap length, as well as the logic for when that condition is met. data notes; input text $65.; cards; patient is not homeless she said that she is currently working and it no longer homeless he is afraid that he will become homeless at the end of the month he is going to be homeless Patient description: Homless? No ; run; data want; set notes; if prxmatch('/(.*)(HOMELESS(.{1,10}?)NO)|(NO(.{1,10}?)HOMELESS)(.*)/', upcase(text)) then contains_homeless_and_no = 'yes'; else contains_homeless_and_no = 'no'; run; Edit: use the input data @mar0000  provided. (Note, the last record has the word "Homless"... Not sure if that typo is intentional, or to be included when looking for "homeless".)  ... View more

Is this what you're looking for? /* Input data */ data input; input start_date :date9. end_date :date9. leave_count; format start_date end_date date9.; datalines; 06AUG2018 10AUG2018 5 13AUG2018 14AUG2018 2 17AUG2018 17AUG2018 1 30DEC2019 03JAN2020 5 ; run; /* Then, split up this input data with one row per day */ data input_split_by_days (drop=i); set input; do i = 1 to leave_count; leave_count = 1; output; start_date = intnx('day', start_date, 1, 's'); end; run; /* Holiday data */ data holidays; input holiday_date :date9.; format holiday_date date9.; datalines; 01JAN2020 06JAN2020 25APR2020 01MAY2020 ; run; /* Join the input data (split up by days) to the holiday data. Only keep records where the start_date is not a holiday. */ proc sql; create table want as select i.* from input_split_by_days i left join holidays h on i.start_date = h.holiday_date where h.holiday_date is null; quit;     ... View more

Are these numeric values or character strings? If numeric, try this: num2 = round(original_num, 0.1); If character strings, try this: num2 = round(input(original_num, 8.3), 0.1);   ... View more

If you're just looking for the sum of BDTOT for each HRR_Code, then this should do it: proc sql; create table want as select HRR_Code, sum(BDTOT) as total_beds_by_hrr format comma8. from have group by HRR_Code; quit;   ... View more