Hi, Well, I have never seen that type of cards statement use before.  Unusual.  Personally I would agree with Chrishi, one step approach.  Firstly define what you want your output dataset to look like - the attrib statements - then have an infile statement to define delimiter (because if you have more than 10 rows of data you don't want to be typing variable names each time - unless you like typing?).  Then input the data.  This approach would also work entering data into Excel for instance and then importing, just change the infile from cards to a filename. ... View more

Then your putting something wrong in the YOUR_LIBNAME, you need to put a working library which has datasets within it, for example if I wanted sashelp then: % let your_libname=sashelp; Its where your datasets are. ... View more

Yep, follow good programming guidance / structured programming.  E.g. declare macro variables either at the start of the program, for global ones, or within their scope for local ones.  Store macros with guidance documentation in a protected library area, so you can ensure the ones you use are validated ones.  Many other things can help organize the code and make it more readable/maintainable and their are several website dedicated to this topic although this type of thing doesn't seem to have come to SAS programming that much as yet: Wikipedia definition: http://en.wikipedia.org/wiki/Best_Coding_Practices You could also run your programs in batch mode, this means tha SAS would startup, run your program, then close - hence not keeping over anything from previous runs.  Other than that I don't think there is a (at least simple) way of cleaning you SAS run. ... View more

Well, as I added I don't think binary XLS files will work with infile.  I would still recommend converting to CSV and then algorithmically removing records/columns you don't need. You could try proc import each one and then proc append the result, and if your very likely you may end up with what you want.  I would still look at the process I have given, as there are many trip ups - number stored a character, data hidden behind Excel formats, special characters, multi-line fields etc. any of which could give you an invalid end result.  Try it and see: %macro imp (fname=);      proc import datafile="...\&fname." out=tmp;           sheet="...";           range="...;      run;      proc append base=base data=tmp;      run; %mend imp; /* Do first one so we have base */      proc import datafile="...\..." out=base;           sheet="...";           range="...;      run; /* Do others */ %imp (fname=...); %imp (fname=...);     ... View more

Well, several questions: Are the files XLS or XLSX? Why not convert them to CSV - simple VBA script - as file format easier to work with. Do each of the sheets contains *exactly* the same data, e.g. same columns/formats.  Otherwise you will have problems setting them to one table. Do you have mixed datatypes in your Excel columns, i.e. do you have columns with number and alphanumerics. Do you have a structured data import documentation, i.e. what will you do with oddities - for example date column with partial dates, results with character results. Once you have you plan on how each data element from each datafile should be handled then go about the process of grouping similar file structures and simple tools.  Create an import program for each different file where there are significant differences, or alternatively read everything in a long character and then post-process the data. The reason for all of the above is that Excel is not a database/data transfer/or anything else, and hence will cause you lots of headaches. To add, I don't think you will be able to infile binary .XLS files as you have been doing, these would need proc import.  Again, another good reason to save your data as CSV (basic text file with delimiters), which you can then use as you want. ... View more