I am looking for a best way to process my data and export to excel so the users will have flexibility to filter and get the summary counts. So far what I have done in my SAS program is:  I applied all the filters and the business rules and created a line list. Using Excel engine I wrote this line list to a pre-existing Excel template where I created a pivot table with all the row and column counts and grand total along with standard bar graphs. When I run this every afternoon the line list is overwriting the existing Excel table so my pivot table is not refreshing with new data.  Running SAS on local machine (windows environment). Open to any suggestions to give flexibility to my users to filter by start date, end date, dept. etc and get the overall counts.   Thank  you ... View more

How can I exclude only 1, 5 and 7 records..   data temp_copy; infile cards dlm='|' truncover ; input subDate :mmddyy10. TestID :$5. LogDept :$2. CurrentDept :$2. status :$8. Category $4.; format subDate yymmdd10.; cards;   10/21/2020|29545|54|99|Active|K12 10/21/2020|29545|54|99|Active|K6 10/21/2020|29545|54|99|Active|K9 10/30/2020|56545|45|99|Active|K12 10/30/2020|56545|45|99|InActive|K12 10/30/2020|56545|45|88|Active|K12 11/21/2020|49545|33|99|Active|K12 11/21/2020|49545|33|99|Active|K9 11/21/2020|99546|56|94|shipped|K6 11/21/2020|99546|56|94|shipped|K9 11/21/2020|99546|56|99|shipped|K9 ;   proc sql; create table test as select distinct testid, subDate, CurrentDept, Status, category from temp_copy order by testid, subDate, CurrentDept, Status, category   ; quit; data lastcategory; set test; by testid subDate CurrentDept Status category; if last.CurrentDept; if CurrentDept= '99' and Status="InActive" then delete; run; /*conditions to check.. If TestId is in 2 or more categories then count only in K6 & K9, because K12 is the parent and should be excluded; but if TestId is in only K12 category and in 2 different currentDept then keep both the records. If CurrentDept='99' and status='InActive' then delete;*/ ... View more

I have a specific requirement. Keep all the duplicate records if the group is different except in one instance If the record is unique with Group='Other' then keep this otherwise delete all duplicate records where group='Other'. Always take the most recent subDate data to_clean; infile cards dlm='|' truncover ; input subDate :mmddyy10. unitName :$100. ADDR1 :$100. group $4.; format subDate yymmdd10.; cards; 11/21/2020|BAIRD HUDSON ENTERPRISES|106 E MAIN|Other 10/30/2020|BAIRD HUDSON ENTERPRISES|106 E MAIN STREET|TNF 10/30/2020|BIG HORN ENTERPRISE|146 S. BENT STREET|SNF 10/30/2020|BIG HORN ENTERPRISE|641 WARREN STREET|SNF 11/5/2020|BROOKDALE |tt|ALF 10/29/2020|BROOKDALE|2401 COUGAR AVENUE|ALF 10/30/2020|ELMCROFT|1551 SUGARLAND DRIVE|ALF 11/2/2020|ELMCROFT|1551 SUGARLAND DRIVE DRIVE|SNF 11/21/2020|GREEN HOUSE LIVING|2311 SHIRLEY|SNF 10/29/2020|GREEN HOUSE LIVING|2311 SHIRLEY COVE|ALF 11/21/2020|MISSION AT THE VILLA|1445 UINTA|ALF 11/2/2020|MISSION AT THE VILLA|1445 UINTA DRIVE|Other ... View more

This is extension to my previous question. I would like to output last. if the group value is same. If the group value is different I want to retain both records.(keep both Bold records below)   data to_clean; infile cards dlm='|' truncover ; input subDate :mmddyy10. unitName :$100. ADDR1 :$100. group $4.; format subDate yymmdd10.; cards; 11/21/2020|BAIRD HUDSON ENTERPRISES|106 E MAIN|TNF 10/30/2020|BAIRD HUDSON ENTERPRISES|106 E MAIN STREET|TNF 10/30/2020|BIG HORN ENTERPRISE|146 S. BENT STREET|SNF 10/30/2020|BIG HORN ENTERPRISE|641 WARREN STREET|SNF 11/5/2020|BROOKDALE |tt|ALF 10/29/2020|BROOKDALE|2401 COUGAR AVENUE|ALF 10/30/2020|ELMCROFT|1551 SUGARLAND DRIVE|ALF 11/2/2020|ELMCROFT|1551 SUGARLAND DRIVE DRIVE|SNF 11/21/2020|GREEN HOUSE LIVING|2311 SHIRLEY|SNF 10/29/2020|GREEN HOUSE LIVING|2311 SHIRLEY COVE|ALF 11/21/2020|MISSION AT THE VILLA|1445 UINTA|ALF 11/2/2020|MISSION AT THE VILLA|1445 UINTA DRIVE|ALF ; proc sort data=to_clean; by unitName subDate; run; proc sql; create table to_clean_new as select * from to_clean order by unitName, subDate, group; quit; data to_clean_2; set to_clean_new; by unitName; length goodAddr $100; retain goodAddr; if first.unitName then goodAddr = addr1; else if complev(trim(goodAddr), trim(addr1), "IL:") > 2 then goodAddr = addr1; run; proc sort data=to_clean_2; by unitName goodAddr subDate group; run; data want; set to_clean_2; by unitName goodAddr; if last.goodAddr; run; proc print noobs data=want; run; **** Thank you ... View more