Hello,   I am using the below code to export a sas dataset to an Excel worksheet.  If the workbook doesn't exist then it is created along with the sheet and everything works as intended.  However, if I change the work.HAVE dataset and re-run the export the SAS log tells me that "'WANT' range/sheet was successfully created" but the data in the sheet doesn't actually change and neither does the modified date on the Excel file.  I am working in SAS Enterprise Guide 7.15 and Excel 2016.  Any insight is greatly appreciated.  Thanks in advance.   proc export data= work.HAVE outfile = "&path\test.xlsx" dbms= EXCEL replace; sheet= "WANT"; run; ... View more

I have a dataset that has one field where the values are 2017_12on15 2018_12on15 2019_12on12 2020_3on3 and then some other values that don't fit this pattern.  I want to select these values into macro variable but I can't seem to get it to work. Here is what I have that isn't working.  Probably something simple that I am missing.  Any insight would be appreciated. proc sql noprint; select FIELD1 into :MACRO_VAR separated by "|" from work.HAVE where FIELD1 like '20__\_%on%'; quit;   ... View more

Someone is running data for me and saving the output in a sub folder of a Yearly directory.  For example, I have a folder called "2020" and the data that is being run for me gets output to subfolders called "Jan062020", "Jan132020", "Jan202020", etc. and within each of these folders are a couple of datasets that always has the same names.    What I'd like to do is create a list of these sub folders that I can then loop over and pull in the datasets into an aggregated file.   I've spent some time Googling but can't find anything that fits my inquiry so I am hoping to find assistance here.   Thanks in advance for your time. ... View more

proc sql; create table WANT as select REGION, SUM(SALES) as SALES, SUM(PROFIT) as PROFIT, IFN(CALCULATED SALES in (0,.) or CALCULATED PROFIT in (0,.), 0, ROUND(PROFIT / SALES, 0.01)) as MARGIN from HAVE group by 1; quit; I am trying to do a calculation and I thought the code I have would eliminate any missing values in the results since I am checking for missing values before I do the ROUND(PROFIT / SALES, 0.01) calculation.  However, when I go to the log, I see the following:   NOTE: Invalid (or missing) arguments to the ROUND function have caused the function to return a missing value.   I want to have zeroes in the output and not missing values and I thought the code I have would produce that but unfortunately it is not.   Any help understanding why would be appreciated.  Thanks in advance.  ... View more

I want to export a SAS dataset from UNIX server to LAN as an Excel file but I want the Excel workbook to be created if it doesn't already exist.    The first export works and the ".xls" file is created when it doesn't exist.   However, I am concerned that the dataset might exceed the ~ 65000 rows in a ".xls" file so I would prefer to be able to export the data as ".xlsx".    The second export does not work when I try to export as ".xlsx" file unless I manually create a blank workbook called "Want.xlsx" before running the export.   I am working in SAS Enterprise Guide 6.1.  Any help would be greatly appreciated.  Thanks in advance. PROC EXPORT DATA= work.HAVE OUTFILE= "\\LAN\WANT.xls" DBMS= EXCELCS REPLACE; SERVER=SERVERNAME; PORT=0001; SERVERUSER="USRDOM\&USER"; SERVERPASS="&PW"; SHEET= "OUTPUT"; RUN; PROC EXPORT DATA= work.HAVE OUTFILE= "\\LAN\WANT.xlsx" DBMS= EXCELCS REPLACE; SERVER=SERVERNAME; PORT=0001; SERVERUSER="USRDOM\&USER"; SERVERPASS="&PW"; SHEET= "OUTPUT"; RUN;   ... View more

I would like to change read/write permission for some folders I have on a server.  I know that I can use x commands in SAS EG to do this and commands like x cd '/filepath/'; and x pwd; both run and print output to the log.  However, when I try to run x ls -l; so I can see the current permissions of folders in the directory, nothing prints to the log.  Conceptually, this is all new to me so I am sure I am missing something simple.   Thanks in advance for your help.   ... View more

The first macro below I got from SAS website.  I want to eliminate the %let start=, %let end=, and %let dif=, statements and just have the INTCK calculation done in the "%do i = 0 %to" line.  If I make substitutions one at a time, the code works fine up until I attempt to substitute "%let start =" piece.  If you try to run the code below, the first 3 will work but the last one will not.    Please help me understand why the last code will not work.  Thanks in advance. * THIS IS THE ORIGINAL FILE FROM THE SUPPORT.SAS WEBSITE;   %macro date_loop(start,end); %let start=%sysfunc(inputn(&start,anydtdte9.)); %let end=%sysfunc(inputn(&end,anydtdte9.)); %let dif=%sysfunc(intck(month,&start,&end)); %do i=0 %to &dif; %let date=%sysfunc(intnx(month,&start,&i,b),date9.); %put &date; %end; %mend date_loop; %date_loop(01jul2015,01feb2016); * THIS DOES THE &dif CALCULATION IN THE DO LOOP;   %macro date_loop(start,end); %let start=%sysfunc(inputn(&start,anydtdte9.)); %let end=%sysfunc(inputn(&end,anydtdte9.)); %do i=0 %to %sysfunc(intck(month,&start,&end)); %let date=%sysfunc(intnx(month,&start,&i,b),date9.); %put &date; %end; %mend date_loop; * THIS SUBSTITUTES THE "%let end=" LINE INTO THE 3RD ARGUMENT OF THE INTCK FUNCTION; %macro date_loop(start,end); %let start=%sysfunc(inputn(&start,anydtdte9.)); %do i=0 %to %sysfunc(intck(month,&start,%sysfunc(inputn(&end,anydtdte9.)))); %let date=%sysfunc(intnx(month,&start,&i,b),date9.); %put &date; %end; %mend date_loop; * THIS SUBSTITUTES THE "%let start=" LINE INTO THE 2RD ARGUMENT OF THE INTCK FUNCTION; %macro date_loop(start,end); %do i=0 %to %sysfunc(intck(month,%sysfunc(inputn(&start,anydtdte9.)),%sysfunc(inputn(&end,anydtdte9.)))); %let date=%sysfunc(intnx(month,&start,&i,b),date9.); %put &date; %end; %mend date_loop;       ... View more