Hi,   Attaching a excel for reference. Please have a look at it.   Thanks ... View more

%let path= c:\temp ; data want; rc=filename('x',"&path"); did=dopen('x'); do i=1 to dnum(did); fname=dread(did,i); if prxmatch('/\d{8}\.xlsx/',fname) then do; want=scan(fname,-1,,'kd');output; end; end; drop rc did i; run; ... View more

LIBNAME myEXCEL2 XLSX 'C:\somewhere\MySpreadsheetA.XLSX; LIBNAME myEXCEL2 XLSX 'D:\somewhere\MySpreadsheed2.XLSX; Data combined; set set myExcel1.TabName1 myExcel1.TabName2 myExcel2.TabNameX myExcel2.TabNameZ; Run; *https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acpcref/n0oj9f6i838mymn148890ckla700.htm ... View more

Your problem description is missing a lot of pertinent issues. So let's assume that you want to run a single data step that reads all of the files using a wildcard in the INFILE statement. So first make sure you know how to tell which file you are reading (and so when you change files). When starting a new file check if the it is one you want to read or skip. data want; length fname $256 ; infile "myfiles*.txt" filename=fname truncover dsd ; input @; if fname ne lag(fname) then do; if indexc(_infile_,'09'x) then skip=1; else skip=0; end; if not skip then do; input .... ; output; end; run; If the issue is that the data is same but sometimes they use tab and sometimes they use comma then you can just modify the value used by the DLM option of the infile statement. data want; length dlm $1 ; infile "myfiles*.txt" truncover dsd dlm=dlm; input @; if indexc(_infile_,'09'x) then dlm='09'x; else dlm=','; input .... ; run; ... View more

Hi: You'll have to figure out how you want to do it. One way is to use the H= option of the TITLE statement to make the title a smaller font: title1 h=8pt 'Really long title'; And the other way would be to use a sub-option that tells TAGSETS.EXCELXP to use more cells for the title. By default, the title spans the number of cells in the table, so with tables that only have 2 or 3 or 5 columns, your title may end up wrapping. I believe there is a sub-option that lets you specify how many cells to use for the titles/footnotes. If you don't know how to see the sub-options, the next time you invoke the code, add this: ods tagsets.excelxp file='......' options(doc='Help'); The doc='Help' sub-option will put all the possible sub-options into the SAS log. I can't remember now whether it is TITLE_WIDTH or TITLE_FOOT_WIDTH or something similar. Maybe TITLE_FOOTNOTE_WIDTH. Worth investigating. Cynthia ... View more

Hello,   I think you can achieve what you want in a more readable way and with less problems by not using macros but plain SAS language.   Here is an example based on the code you've given, that you can adapt to your needs. proc format; invalue denom 'A', 'F', 'M', 'O', 'Q'=100 'P'=1000 'G', 'J'=10000 'H', 'OK'=100000 other=0; run; %let PLN=PLN; %let CDD_FMT_&PLN._1=G; %let CDD_FMT_&PLN._2=A; data want; length CDD_FMT $2.; CDD_TX_PLN_1="5000"; /* For the example */ CDD_TX_PLN_2="200"; format CDD_VL_PLN_1 CDD_VL_PLN_2 best.; array TX CDD_TX_PLN:; array VL CDD_VL:; do W=1 to 2; CDD_FMT=symget(cats("CDD_FMT_&PLN._",W)); denom=input(CDD_FMT, denom.); CDD_TX=input(TX(W),30.); if denom then VL(W)=CDD_TX/denom; else if CDD_FMT='D' then VL(W)= input(TX(W), YYMMDD10.); else VL(W)=CDD_TX; end; drop denom CDD_FMT; run; ... View more