Not sure if it helps but we had a similar issue with drop downs (rather than list box) with dates.   We ended up creating an extra column in data prep where newest date was shown as 'latest' rather than the actual date (we made it a text field), we then selected that in the report and saved so that 'latest' was always the default selected option ... View more

Deleting 15m+ rows in SQLServer shouldn't be a big deal.   There's loads of ways to attack this but I would start of by looking at your indexes, they can slow things down a lot in some circumstances. Sometimes it can be better to drop indexes then recreate after the operation.    Have you run the estimated execution plan in sql management studio when doing the delete  there? might give some tips   I'd also consider looking at using transactions so things roll back rather than end up with a random amount of remaining data ... View more

Hi,   How are you loading your LASR tables? Enterprise Guide, DI or Query Builder?         ... View more

  Sample attached ... View more

Hi @SASKiwi,   The initial data source is an xml file, my map file has (as an example - mostly was auto generated) <COLUMN name="BIRTHDTE"> <PATH syntax="XPath">/StaffRecord/Institution/Person/BIRTHDTE</PATH> <TYPE>numeric</TYPE> <DATATYPE>date</DATATYPE> <FORMAT width="10">IS8601DA</FORMAT> <INFORMAT width="10">IS8601DA</INFORMAT> </COLUMN> Not sure why but running the create table statement using that source produces a date/time output in my sql server database.  ... View more

Hi guys,   Thanks for the tips, I tried to play with both but struggled to get it working, ended up using "put" to covert to text format which is not ideal but better than where I was.   I still can't understand why creating the table using the SAS data set worked fine but then inserting from the same data set (after refreshing) fell over, guess its some kind of SAS vs MS interoperability thing. ... View more

Need some more detail, are you talking about stored processes?   If so could you use parameters to specify which parts of the code are executed? ... View more

Thank you, that actually helps alot as I don't feel such a noobie     I'll speak to our SAS rep and see what they say.      ... View more

Still getting a new worksheet with a new range   log   1 The SAS System 20:19 Thursday, September 21, 2017 1 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 5 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "F:\SASWORK\_TD28392_ISS-SAS01_\Prc2/" SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 31 32 libname out xlsx "Path\Template_test2.xlsx"; NOTE: Libref OUT was successfully assigned as follows: Engine: XLSX Physical Name: Path\Template_test2.xlsx 33 34 %macro create_yourdata; 35 %do i=1 %to 1;*change for max obs; 36 37 data yourdata_&i (keep=items); 38 set new3; 39 if flag=&i; 40 run; 41 42 /* proc export data=yourdata_&i*/ 43 /* file=output*/ 44 /* dbms=XLSX replace;*/ 45 /* Range='SAS_RANGE';*/ 46 /* run;*/ 47 48 /*proc sql;*/ 49 /*drop table out.SAS_RANGE;*/ 50 /*quit;*/ 51 52 data out.SAS_RANGE; 53 set yourdata_&i ; 54 run; 55 56 %end; 57 options mprint symbolgen; 58 %mend create_yourdata; 59 %create_yourdata; MPRINT(CREATE_YOURDATA): *change for max obs; SYMBOLGEN: Macro variable I resolves to 1 MPRINT(CREATE_YOURDATA): data yourdata_1 (keep=Items); MPRINT(CREATE_YOURDATA): set new3; SYMBOLGEN: Macro variable I resolves to 1 MPRINT(CREATE_YOURDATA): if flag=1; MPRINT(CREATE_YOURDATA): run; 2 The SAS System 20:19 Thursday, September 21, 2017 NOTE: There were 11133 observations read from the data set WORK.NEW3. NOTE: The data set WORK.YOURDATA_1 has 226 observations and 12 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds MPRINT(CREATE_YOURDATA): data out.SAS_RANGE; SYMBOLGEN: Macro variable I resolves to 1 MPRINT(CREATE_YOURDATA): set yourdata_1 ; MPRINT(CREATE_YOURDATA): run; WARNING: Some character data was lost during transcoding data: Data 5: 2012/13-2016/17: Value (£) of successful grant applications for 1st August 2012 to 31st July 2017 . NOTE: There were 226 observations read from the data set WORK.YOURDATA_1. NOTE: The data set OUT.SAS_RANGE has . observations and 12 variables. NOTE: The export data set has 226 observations and 12 variables. NOTE: DATA statement used (Total process time): real time 0.39 seconds cpu time 0.14 seconds MPRINT(CREATE_YOURDATA): options mprint symbolgen; 60 61 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 76 77 78 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 81 ... View more

Log when using sheet   1 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 5 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "F:\SASWORK\_TD28392_ISS-SAS01_\Prc2/" SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 31 32 filename output "\\path\Template_test2.xlsx"; 33 34 35 36 %macro create_yourdata; 37 %do i=1 %to 1;*change for max obs; 38 39 data yourdata_&i (keep=index2 employee_number index value description is_total is_department_total is_title 39 ! is_projectable is_data_not_found resource_id kpi_set); 40 set new3; 41 if flag=&i; 42 run; 43 44 proc export data=yourdata_&i 45 file=output 46 dbms=XLSX replace; 47 Sheet='SAS_RANGE'; 48 run; 49 50 %end; 51 options mprint symbolgen; 52 %mend create_yourdata; 53 %create_yourdata; MPRINT(CREATE_YOURDATA): *change for max obs; SYMBOLGEN: Macro variable I resolves to 1 MPRINT(CREATE_YOURDATA): data yourdata_1 (keep=index2 employee_number index value description is_total is_department_total is_title is_projectable is_data_not_found resource_id kpi_set); MPRINT(CREATE_YOURDATA): set new3; SYMBOLGEN: Macro variable I resolves to 1 MPRINT(CREATE_YOURDATA): if flag=1; MPRINT(CREATE_YOURDATA): run; NOTE: There were 11133 observations read from the data set WORK.NEW3. NOTE: The data set WORK.YOURDATA_1 has 226 observations and 12 variables. NOTE: DATA statement used (Total process time): real time 0.02 seconds cpu time 0.01 seconds SYMBOLGEN: Macro variable I resolves to 1 MPRINT(CREATE_YOURDATA): proc export data=yourdata_1 file=output dbms=XLSX replace; 2 The SAS System 20:19 Thursday, September 21, 2017 MPRINT(CREATE_YOURDATA): WXLX; MPRINT(CREATE_YOURDATA): Sheet='SAS_RANGE'; MPRINT(CREATE_YOURDATA): run; NOTE: The export data set has 226 observations and 12 variables. NOTE: "OUTPUT" file was successfully created. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.39 seconds cpu time 0.17 seconds MPRINT(CREATE_YOURDATA): options mprint symbolgen; 54 55 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 70 71 72 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 75 log when using range 1 The SAS System 20:19 Thursday, September 21, 2017 1 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 5 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "" SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 31 32 filename output "Path\Template_test2.xlsx"; 33 34 35 36 %macro create_yourdata; 37 %do i=1 %to 1;*change for max obs; 38 39 data yourdata_&i (keep=items; 40 set new3; 41 if flag=&i; 42 run; 43 44 proc export data=yourdata_&i 45 file=output 46 dbms=XLSX replace; 47 Range='SAS_RANGE'; 48 run; 49 50 %end; 51 options mprint symbolgen; 52 %mend create_yourdata; 53 %create_yourdata; MPRINT(CREATE_YOURDATA): *change for max obs; SYMBOLGEN: Macro variable I resolves to 1 MPRINT(CREATE_YOURDATA): data yourdata_1 (keep=items); MPRINT(CREATE_YOURDATA): set new3; SYMBOLGEN: Macro variable I resolves to 1 MPRINT(CREATE_YOURDATA): if flag=1; MPRINT(CREATE_YOURDATA): run; NOTE: There were 11133 observations read from the data set WORK.NEW3. NOTE: The data set WORK.YOURDATA_1 has 226 observations and 12 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds SYMBOLGEN: Macro variable I resolves to 1 MPRINT(CREATE_YOURDATA): proc export data=yourdata_1 file=output dbms=XLSX replace; 2 The SAS System 20:19 Thursday, September 21, 2017 MPRINT(CREATE_YOURDATA): WXLX; NOTE: Line generated by the invoked macro "CREATE_YOURDATA". 53 file=output dbms=XLSX replace; Range='SAS_RANGE'; run; _____ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. MPRINT(CREATE_YOURDATA): Range='SAS_RANGE'; NOTE: The previous statement has been deleted. MPRINT(CREATE_YOURDATA): run; NOTE: The export data set has 226 observations and 12 variables. NOTE: "OUTPUT" file was successfully created. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.40 seconds cpu time 0.18 seconds MPRINT(CREATE_YOURDATA): options mprint symbolgen; 54 55 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 70 71 72 %_eg_hidenotesandsource; MPRINT(_EG_HIDENOTESANDSOURCE): options nonotes; MPRINT(_EG_HIDENOTESANDSOURCE): options nosource; SYMBOLGEN: Macro variable _EGNOTES resolves to NOTES MPRINT(_EG_RESTORENOTESANDSOURCE): options NOTES; SYMBOLGEN: Macro variable _EGSOURCE resolves to SOURCE MPRINT(_EG_RESTORENOTESANDSOURCE): options SOURCE; 75 ... View more

Hi,   Thank you for your patience with this,   The code I tried for range is below,    filename output "Path\Template_test2.xlsx"; %macro create_yourdata; %do i=1 %to 1;*change for max obs; data yourdata_&i (keep=items); set new3; if flag=&i; run; proc export data=yourdata_&i file=output dbms=XLSX replace; Range='SAS_DATA'; run; %end; %mend create_yourdata; %create_yourdata; Error is: ERROR 180-322: Statement is not valid or it is used out of proper order. (with Range highlighted)   I have tried several different approaches (a colleague has tried as well) all have given similar results, we can get EG to create a new named range but not populate an existing one. Must be something to do with our setup. ... View more

Quick update:   I think I have worked out whats going wrong.   SAS seems to need the worksheet and the range to have the same name. It seems to delete the existing sheet and create a new one with a new named range. I was hoping it would write to a pre-existing range so that I could have multiple ranges on one sheet and also not need to have everything positioned starting at cell A1 (our templates are all hardcoded to look in specifiec locatons- we need to change this anyway but its going to be time consuming.)   If anyone has any other suggestions as to how I can write output to specifc cells and worksheets then I'd be really interested to test them out. ... View more