I assume you mean OPTIONS PAPERSIZE: 1    OPTIONS PAGESIZE = 'ISO A4';              --------              18 ERROR 18-12: Option value for SAS option PAGESIZE must be between 15 and 32767. 2    OPTIONS PAPERSIZE = 'ISO A4'; ... View more

Hi , The orientation is fine in my Excel XML file, although I'm happy to move that setting to the tagset sub-option if that would help.  The problem is with the papersize, which is not being set.  I checked the tagsets doc and didn't see a papersize sub-option. If you submit my code, open the XML output in Excel, Print Preview, Page Setup, is the papersize set to A4 and A3 for the two worksheets respectively? My actual Excel workbook will have many worksheets, some "wide" and some "narrow".  It will be really helpful to the end user if I can set the papersize programmatically, so they can "just print" from Excel without having to fiddle with options. Regards, Scott ... View more

Read the macro headers, including the numerous use cases. Read the SAS 9.3 (or 9.4) documentation on the %put macro statement. Post back then if you still have questions. ... View more

Download Richard Devenezia's seplist macro:  http://www.devenezia.com/downloads/sas/macros/index.php?m=seplist Or, download my slightly modified version:  SAS/seplist.sas at master · scottbass/SAS · GitHub Also download my loop macro from my GitHub site Lots of good macros on both sites Then, you can do: %let varlist=%str(var1, var2, var3); %put %seplist(%superq(varlist),indlm=%str(,),prefix=a.); * I hate commas in a macro variable list. That's why I have to use %superq above.; * Don't use them, let %seplist add them later at "run time" ; %let varlist= var1 var2 var3 ; %let newlist = %seplist(&varlist,prefix=a.); %put &=newlist; * Or, use %loop, which is more complex, but can convert both ways ; %macro newlist; %* convert oldlist to newlist ; %if (&__iter__) gt 1 %then %str(,); a.&word %mend; %macro oldlist; %* convert newlist to oldlist ; %scan(&word,2,.) %mend; %let varlist=varA varB varC; %let newlist = %loop(&varlist,mname=newlist); %put &=newlist; %let oldlist = %loop(%superq(newlist),dlm=%str(,),mname=oldlist); %put &=oldlist; Here are the results: 3    %let varlist=%str(var1, var2, var3); 4    %put %seplist(%superq(varlist),indlm=%str(,),prefix=a.); a.var1,a.var2,a.var3 5 6    * I hate commas in a macro variable list. ; 7    * Don't use them, let %seplist add them later at "run time" ; 8 9    %let varlist= 10   var1 11   var2 12   var3 13   ; 14   %let newlist = %seplist(&varlist,prefix=a.); 15   %put &=newlist; NEWLIST=a.var1,a.var2,a.var3 16 17   * Or, use %loop, which is more complex, but can convert both ways ; 18   %macro newlist; 19   %* convert oldlist to newlist ; 20   %if (&__iter__) gt 1 %then ,; 21   a.&word 22   %mend; 23   %macro oldlist; 24   %* convert newlist to oldlist ; 25   %scan(&word,2,.) 26   %mend; 27 28   %let varlist=varA varB varC; 29   %let newlist = %loop(&varlist,mname=newlist); 30   %put &=newlist; NEWLIST=a.varA , a.varB , a.varC 31 32   %let oldlist = %loop(%superq(newlist),dlm=%str(,),mname=oldlist); 33   %put &=oldlist; OLDLIST=varA varB varC I would just use %seplist unless I needed to convert back to the original list and for some reason didn't preserve the original list. %seplist is REALLY handy for converting between data step and SQL syntax.  I used it ALL the time. HTH... ... View more

"So I'm planning to keep the CSV files as is."  I'm not sure what this means???  Are you planning to keep your source CSV files as is, your target CSV files as is (which doesn't make sense, since they don't exist yet), or your CSV code generation program as is? You say you "have a strong preference to keep all processing/analysis in a single platform", yet ask a question in a SAS forum, then say you've got the code in R? Anyway, given your problem statement above, and with lots of assumptions, here is what I think you want to do, using SAS: * create some dummy CSV files. ; * assumption: the files have the same structure, just different data ; proc export data=sashelp.stocks (where=(stock="IBM")) file="c:\temp\_ibm.csv" dbms=csv; run; proc export data=sashelp.stocks (where=(stock="Intel")) file="c:\temp\_intel.csv" dbms=csv; run; proc export data=sashelp.stocks (where=(stock="Microsoft")) file="c:\temp\_microsoft.csv" dbms=csv; run; * merge (append) the CSV files, setting the source of the file as a variable ; filename csvs "c:\temp\_*.csv";  * note the wildcard in the filename statement ; data test;   length fullpath $200 source $20;   infile csvs filename=fullpath eov=eov dsd dlm=",";   * skip the header record on the first row of each file ;   input @@; * input and hold the record pointer, used to set eov ;   if _n_=1 or eov then do;  * eov is not set on _n_=1 ;     eov=0;  * need to reset eov, SAS doesn't do this automatically ;     input;  * release the record pointer for the next read ;     delete; * throw away the header record ;   end;   else do;     input stock $ date open high low close volume adjClose;   end;   * parse the fullpath to get just the source basename ; source=scan(fullpath,-1,"\"); source=scan(source,1,".");   * input informats ;   informat date date. open high low close adjClose dollar. volume comma.;   * output formats ;   format date date. open high low close adjClose dollar12.2 volume comma12.; run; ... View more

Here's my/yet another approach: * problem as stated ; data have;   input a b c $ Zip d e f;   cards; 1 2 x 12345 6 7 8 2 3 y 54321 7 8 9 ; run; %let vars=a b c d e zip; data want1;    format &vars;    set have (keep=&vars); run; * more realistic example ; data have;   input b c $ f Zip d a e;   cards; 2 x 8 12345 6 1 7 3 y 9 54321 7 2 8 ; run; %let vars=a b c d e zip; data want2;    format &vars;    set have (keep=&vars); run; proc compare base=want1 compare=want2; run; * last approach, use a "skeleton" dataset as metadata ; data skel;   length a b 8 c $8 d e Zip 8;  * these must be in the desired order for the target dataset ;   call missing(of _all_);       * could also add formats, labels, etc. ;          stop; run; proc contents data=skel out=columns (keep=name varnum) noprint; run; proc sql noprint;   select name into :vars separated by " " from columns order by varnum; quit; data want3; * if any of your metadata columns are derived columns (i.e. not in your source dataset) ; * remember the implied retain on dataset variables, or this can come back to bite you! ; * IOW if skel contains a variable "foo" in order to set its PDV order, ; * and "foo" is calculated in the data step, remember that it is retained in the data step. ;   if 0 then set skel;   set have (keep=&vars); run; proc compare base=want1 compare=want3; run; For the problem as stated, where your source data has the PDV in almost the exact order you want in the target dataset, approaches such as variable lists may use less code.  However, they are prone to undesired results if the source data structure changes. In a perhaps more realistic example where the source data has its PDV in an order unrelated to the desired target dataset, you're gonna have to list the variables out.  If I had 500 variables, I'd proc print the data from proc contents output, reorder the variables in an editor, and create my program. The last example is yet another approach.  Not the least amount of code, but an approach I've sometimes used in the past. HTH, Scott Added comments about implied retain of dataset variables ... View more

1) You could use Powershell to add ",<filename>" (end of line) or "<filename>," (beginning of line) to your files, while merging them into a single combined file. 2) You could write a VBA module to add this column as it reads multiple files into a single worksheet. 3) You could easily do this using Perl or Python or .Net or ... 4) You could import multiple CSV files into SAS using an aggregated fileref, use the FILEVAR option of the INFILE statement to capture the current filename, parse the full path to get the basename, and write back out the merged CSV file. What is your *final* "landing zone" for your data?  Is it a merged CSV file?  A SAS dataset?  Excel?  Another database?  Depending on your answer, you may be able to skip the merged CSV file part. ... View more