I think you should think about performing multiple PROC IMPORTS, one for each Excel file.  Our user @Tom has an excellent solution that you can template.  First he shows us away to read the filenames in the folder.  See the first part of the code here. Solved: Re: macro to read all files together - SAS Support Communities    Then using the same technique as Tom, adapt your PROC IMPORT code into Tom's second part, as well as the code to add the columns.   There are many ways to skin this cat, mind you.  I tend to use DOSUBL or CALL EXECUTE in the second part, Tomato-Tomaato.  This is another level of programming, writing programs to write programs.  Good luck. ... View more

I coded but I failed, and its getting close to quitting time.  I searched and I found examples like this:   from: SUGI 28: Using Formats and Other Techniques to Complete PROC REPORT Tables (sas.com) proc report data=sugi28.virginia_pop nowd ; column county pct_imputed population pop ; define county / group; define population / analysis format=comma15. "Population"; define pct_imputed/ format=percent9.3; define Pop / computed “Population”; compute pop /char length=23; if percent_imputed.sum > .5 then pop = (right(put(population.sum, comma15.))|| ' !!' ) ; else pop = (right(put(population.sum, comma15.))||' ' ) ; endcomp; rbreak after / summarize ol ; run; Same here: SUGI 26: Format Challenges with PROC REPORT (sas.com)   I believe this can be done using PROC REPORT with conditionals inside compute blocks.  Maybe someone with better PROC REPORT experience can help?   I googled 'site:support.sas.com "PROC REPORT" change format'. ... View more

it did! I didn't think that the descending or ascending made a difference.  My first run of the descending case indicates that if it does make a difference, it's a small difference.    Please excuse my mistake, it's corrected. ... View more

2 1/2 months later... so interesting.  I considered this being like a SQL view, which sorts and is "piped" to a data step that splits. proc sql; create view sorted_view as select * from cars_times_10000 order by msrp; data a(where=(origin='Asia')) e(where=(origin='Europe')) u(where=(origin='USA')) ; set sorted_view; by msrp; run; So I'm so embarrassed now that my system is SO slow.  I benched marked this against Keintz's and this performs 50% longer than his 'best practice" on my system.     This performs equivalent to Keintz's best practice.  I have a new and increased faith in PROC SQL's optimizations. proc sql; create view sorted_view as select * from cars_times_10000 order by msrp; create table a3 as select * from sorted_view where origin='Asia'; create table e3 as select * from sorted_view where origin='Europe'; create table u3 as select * from sorted_view where origin='USA'; quit;   ... View more

data have; infile datalines; input member year A1 - A3; datalines; 123 2019 1 0 1 123 2020 1 0 1 124 2019 1 1 0 124 2020 0 1 0 125 2019 0 1 1 125 2020 1 1 1 126 2019 0 1 1 126 2020 0 0 1 ;;;;;; run; proc format; value FMnM /*Found-Missing-noMatch*/ 1="Missing" /*1,0*/ 2="New?" /*0,1*/ 3="Found" /*1,1*/ 0="NoMatch" /*0,0*/ ; run; proc sql; create table want as select this.member, this.year, this.A1*2+last.A1 format=FMnM. as A1, this.A2*2+last.A2 format=FMnM. as A2, this.A3*2+last.A3 format=FMnM. as A3 from have as this, have as last where this.year=last.year+1 AND this.Member=last.member; quit;   ... View more

I found this gem of a math formula dealing with overlaping units.  Link is here: http://baodad.blogspot.com/2014/06/date-range-overlap.html    Maybe this will help you flag for overlapped time periods.  This code below will not handle overlaps of three or more time-periods, mind you.  In the code, I commented out the "negative" prescription to show how this formula can work.   data have(label='Prescription Data'); length id $10; infile datalines truncover; input Drug:$1 Prescription_Date:MMDDYY8. Days_Supplied:4.0; Prescription_Date_end=Prescription_Date+Days_Supplied-1; format Prescription_Date: mmddyy10.; Datalines; A 11/8/16 7 A 11/15/16 21 A 12/6/16 28 B 3/8/17 30 /*B 4/7/17 -4*/ A 4/3/17 3 A 5/15/17 3 A 6/5/17 3 B 6/14/17 30 A 10/5/17 15 A 10/25/17 15 ;;;; proc sort; by id; run; data next; do until (last.ID); set have; by ID; Prescription_Date_lag =lag(Prescription_Date); Prescription_Date_end_lag=lag(Prescription_Date_end); format Prescription_Date: mmddyy10.; if first.ID then period_overlap=.; else do; period_overlap=Min (Prescription_Date_end_lag - Prescription_Date_lag , Prescription_Date_end_lag - Prescription_Date, Prescription_Date_end - Prescription_Date, Prescription_Date_end - Prescription_Date_lag );/*http://baodad.blogspot.com/2014/06/date-range-overlap.html*/ if period_overlap<1 then period_overlap=0; end; output; end; run;   Drug Prescription_Date Days_Supplied … period_overlap A 11/8/2016 7 … . A 11/15/2016 21 … 0 A 12/6/2016 28 … 0 B 3/8/2017 30 … 0 A 4/3/2017 3 … 2 A 5/15/2017 3 … 0 A 6/5/2017 3 … 0 B 6/14/2017 30 … 0 A 10/5/2017 15 … 0 A 10/25/2017 15 … 0 ... View more

I'm sorry,a s well.  I'm wondering if a re-install is sufficient to fix this.  But before you do that I'm going to spam some top contributors to see if they have an insight.   @Kurt_Bremser @Tom @SASKiwi @PaigeMiller @ballardw ... View more

before getting distracted by the roman numerals, I intended to make use of the SORT FUNCTION since I don't get to use it very much.  Anyway, following my own "Juolas" Metatheory I did not want to assume the categories would be alphabetical.  BUT maybe we can, and we can use SORT.: data have; infile datalines dlm=':'; input ID Categories :$100.; datalines; 1:(i)cyt,(ii)end therapy,(iii)targ 2:(ii)cyt,(iii)targ,(ii)end therapy 3:(iii)end therapy,(i)cyt,(iii)targ 4:iii)targ,(i)cyt,(ii)end therapy 5:(iii)end therapy,(i)cyt,(iii)targ 6:iii)targ,(i)cyt,(ii)end therapy 7:(i)cyt,(ii)end therapy,(iii)targ 8:(ii)cyt,(iii)targ,(ii)end therapy 9:(ii)cyt 10:(iii)targ 11:(ii)end therapy ; data want1; set have; length cat1-cat3 $20; array cat[3] $ cat1-cat3; do i = 1 to 3;/*assuming 3 categories*/ cat[i] = strip(scan(scan(categories,i,","),2,")")); end; _rc=sort(of cat1-cat3); category=catx(", ",of cat1-cat3); keep id category; run; ... View more

No, I'm just saying I tried to look and solve this problem, before reading your discourse. I noticed the Roman numerals. At the time I was expecting to have to sort the items based on the Roman numerals... ... View more