Hi Apparently my brain works better when I ask questions...but in future I'll ask them privately before posting so I'm not wasting space. So I figured out the issue...I needed to put double quotation marks around the infile filename.  But it still wasn't working and I suspected it wasn't accepting the file as .txt and since there are no delimiters because it's a flat file I couldn't code that.  So I concatenated ".txt" to the fname in the macro i.e. call symput("fname"||trim(left(put(i,8.))),trim(fname)||".txt"); and now it works wonderfully.  At least in test it does. This works great for when I have distinct correction files but I have another format where there is multiple files submitted per year and within the file is a code for "new", "deleted", "updated".  So an abstract could be "new" in April and then resubmitted in July with a correction so the field is "updated".  So I'll need each file to import in sequence and add, update or delete in sequence. I'll see if I can do it based on this exercise but will start a new thread if I do.  Thanks everyone! ... View more

Hi Thanks Xia...sorry but I'm not really good at this so not sure what you mean. Scott, I entered all the code and I see that even though the colours don't change, it's still accepting the code.  BUT, I get an error on the line citing the filename: ERROR: Physical file does not exist, F:\TEST\Data\Corrections\&&fname&i. For the purpose of testing, each file name is only 5 characters:  Corr1, Corr2, and Corr3.  The entire code that is producing the error is: data nxg1; infile 'F:\TEST\Data\Corrections\&&fname&i' truncover LRECL = 5000 firstobs=2; input   @1 Chart $6.             @7 Acct $6.             @13 Sex $1.             @14 Age $2.             @16 DataA $2.             @18 DataB $2.            @20 DataC $2.; run; What do you think the issue is?  Thanks. ... View more

Hi Okay, now I'm having difficulty with the lines to get the data.  You indicate that the first lines should be: %macro imports; I believe there is an error in that the delimiter line has a ; as %do i=1 %to &total.; data nxg1; infile " \\XXXXX\YYYYYY\&&fname&i" delimiter = "09"x ; missover dsd lrecl=32767 firstobs=1;  well as after firstobs but I corrected that in mine and changed the infile location. But it still appears that something isn't closed because the font isn't changing for the run statement, and in fact nothing happens when I run it. My code is as follows: filename files pipe 'dir /b "F:\TEST\Data\Corrections\*.txt"'; data _null_; infile files truncover end=last; input fname $10.;    i+1;    call symput("fname"||trim(left(put(i,8.))),trim(fname));   if last then call symput("total",trim(left(put(i,8.)))); call symput("fnamedate"||trim(left(put(i,8.))),substr(trim(fname),15,8)); run; %macro imports;    %do i=1 %to &total.; data nxg1; infile "F:\TEST\Data\Corrections\&&fname&i" truncover LRECL = 5000 firstobs=2; input    @1 Chart $6.   @7 Acct $6.   @13 Sex $1.   @14 Age $2.   @16 DataA $2.   @18 DataB $2.   @20 DataC $2.; run; Any assistance is greatly appreciated. ... View more

Hi Scott Sorry but just getting around to this again.  I'm getting an error message of "The system cannot find the path specified".  The code that I've used so far to produce the error is: filename files pipe 'F:\TEST\Data\Corrections\*.txt'; data _null_; infile files truncover end=last; input fname $10.;     i+1; call symput("fname"||trim(left(put(i,8.))),trim(fname)); if last then call symput("total",trim(left(put(i,8.)))); call symput("fnamedate"||trim(left(put(i,8.))), substr(trim(fname),15,8)); run; What am I doing wrong?  Thanks. EDIT:  I found the issue, I wasn't including the directory part. So the syntax for the first line for windows should read: files pipe 'dir /b "F:\TEST\Data\Corrections\*.txt"'; ... View more

Hi Reeza Thanks so much for responding. Right now, I'm just importing all submission files to save as file1.  Then I import all correction files and save as file2.  Then I update file1 with file2.  So no looping or anything but as per my scenario above, this could mean that the most recent update isn't being applied OR one correction is updating one data element and the next correction is updating the other but that may not be occurring so I need to have each correction file update file1 and then keep applying each correction file in sequence so the records are updated correctly. Any assistance you can suggest is greatly appreciated. ... View more

Thank you so much for all the really great answers.  I chose Reeza's only because simple is best!  I knew when I posted it the answer would be simple but I could not get the correct syntax. Thanks again. ... View more

Okay thanks...but does it remain as 1, 2, 3 ,4, 5 no matter how many records I have in my database (the sample only had 1)?  I'm asking what it's based on, is it the number of variables in the sample?  So if I have more variables then I must add to this?  Sorry, just not understanding how to extrapolate beyond the one case in my sample data. Thanks. ... View more

Thanks Patrick! For learning purposes, what is the group by doing i.e. do I need it?  I expect to have many, many inpatient visits so I'm not sure if I have to expand that group thus the query. Thanks. ... View more

Hi Okay the data below is a single patient as determined by the chart number.   The data elements are chart number, regno which is unique to the visit, type which is identifying visit type whether ED visit or inpatient, admit date and discharge date. To restate my desired results, I want to have each inpatient visit identifying volumes of ED visits in the last 6 months compared to that inpatient visit.  Therefore, the data would look like this with the last column being the sum of ED visits within the last 6 months of the admission date for the inpatient visit: A1234 005 IP 2012-07-04 2012-07-14  4 A1234 006 IP 2012-09-01 2012-09-07  4 A1234 007 IP 2012-12-24 2012-12-31  1 data test_grp; input @1 chart $5.    @6 regno  $3.    @9 Type $2.    @11 AdmDate yymmdd8.    @19 DisDate yymmdd8. ; format admdate yymmdd10. disdate yymmdd10.; cards; A1234001ED2012032920120329 A1234002ED2012042920120429 A1234003ED2012052920120529 A1234004ED2012062920120629 A1234005IP2012070420120714 A1234006IP2012090120120907 A1234007IP2012122420121231 run; All assistance greatly appreciated. ... View more