I tested on local Windows with PC SAS. With the Excel file open, I could copy it using Windows file explorer and could copy it using copy(). But could be different for Windows server. ... View more

One part of the answer (definitely not the whole answer) is that there is a documented list of formats that are not supported by the Graphics Template Language, and e8601da is on the list of unsupported formats.  https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/grstatgraph/n0o4b33j6ue5bkn1viojbbvwzkba.htm   No clue what about the iso8601 formats would make them difficult to support in GTL.   I'd be curious to see the full code for an example of your third problem.  If adding the xaxis statement with type=time fixed the problem, then it's a good bet that without that, SGPLOT guessed that the axis type was linear.  You can end up with some weird looking plots if SGPLOT guesses the wrong axis type. In below example SGPLOT guesses the axis should be linear, because there is no format attached to the variable.  Using valuesformat will format the xaxis as dates, but note they repeat because it's a linear axis:   data a; input x y; datalines; 1 1 2 2 3 3 4 4 ; run; proc sGPlot data = a; scatter x = x y = y; xaxis valuesformat=date9. ; run;     ... View more

Looks like fcopy() is happy to copy an Excel file while the file is open.  Also copies fine if the file is open and there are unsaved changes to the file.  But it would make sense that when you hit SAVE in Excel, at some point in that process when it writes the file to disk, Excel is going to get an exclusive lock on the file.   If your timing is occasionally unlucky enough that you are trying to copy the file while someone is writing to the file, I think the best you can do is detect the problem and try again. ... View more

Here's an example of the refline hack I mentioned.  Looks like SGPLOT is happy to draw a 0 thickness refline.   proc sort data=sashelp.class out=class ; by age ; run ; proc sgplot data=class ; series x=age y=height ; series x=age y=weight /y2axis; refline 0 /axis=y2 lineattrs=(thickness=0) noclip; *force y2 axis to extend to 0 ; run ; ... View more

I agree with Paige, I would calculate the minimum Y2 value in the data, and if that value is greater than 0, add the y2axis min=0 statement.   If you really don't want to do that, a possible hack would be to add an invisible (meaning 0 weight, or white) refline on the Y2axis at 0.  You would use the /noclip option on the refline to force the y2axis to extend to show the refline.  But that would feel like more work, and more obfuscated code, than conditionally generating the axis statement. ... View more

Can you make a little example, showing the data you have, and the desired output.   Your SQL code will error because you have the option NOSQLREMERGE but the select * will trigger a remerge.  If you change the SELECT statement to select only ID and aggregated variables, your code looks reasonable to me.     Are you trying to create a new variable that is not shown in your code?     data have ; input ID cause_any_inj nat_any_inj; cards ; 1 0 1 1 1 1 2 0 1 2 0 1 ; proc sql noremerge; create table want as select ID ,sum(cause_any_inj) as cause_inj_cnt ,sum(nat_any_inj) as nat_inj_cnt from have group by ID ; quit ; proc print data=want ; run ; ... View more

Take a look at Richard DeVenezia's  %seplist() utility.  It will do that and more.  https://www.devenezia.com/downloads/sas/macros/index.php?m=seplist  ... View more

@Ronein wrote: For some periods the field doesn't exist and gor other exists...I just want to prevent warning here. What is the way?? Please see Tom's answer, and if his answer does not suffice, please explain why it doesn't meet your need. ... View more

Glad you got it working.  Please mark @FreelanceReinh as the correct / accepted answer, to close out this question. ... View more

As a side note to Tom's very thorough explanation (thanks!), many people think the NOTE in the log about automatic type conversion should be treated as an error, to avoid problems like this.  Many log scanners are coded to treat this NOTE as not allowed to appear in a log.   There is an undocumented system option which will allow you to turn this problematic NOTE (and others) into an actual error:   1 options dsoptions=note2err ; 2 data _null_; 3 end_date= '04OCT2014'd; 4 Date1=SUBSTR(End_Date,1,8)*1; ERROR: Numeric value found where character value needed at line 4 column 16. ERROR: Character value found where numeric value needed at line 4 column 9. 5 put (_all_) (=/); 6 run; NOTE: The SAS System stopped processing this step because of errors.       ... View more

You mention upload and download.  Can you describe your environment a bit more?     Is this SAS running on a server, or local PC SAS?  If you've got a SAS dataset sitting on a Windows server, and then you're exporting and downloading a CSV, it could be the the network speed is causing the long run time.  As a test, you could change your code to write the CSV file to the Work library.  That should show how long it takes to write the CSV file, without the overhead of network transfer.  Then perhaps if you determine the issue is network latency, you could work with your admin on that, and perhaps consider compressing the CSV file before it is transferred.   Typically I've found PROC EXPORT to generate a CSV to be fast, so I would give that a try to see if it can handle your data. ... View more

As you say, you can use PROC FORMAT to map one value to another.   Once I had a problem where I needed to map three values into another value.  I started to build a new variable that would have the three values concatenated, and was going to build a format to map the concatenated value to my target value.   Then I remembered that SAS has hash tables.  You can define a hash table that has 3 key values, and use that as a lookup.   As to whether a hash table is "best" or "more efficient", it really depends on your use case.  There are lots of ways to do a lookup in SAS.   ... View more

You've got some good recommendations already.     You could also add a PUT statement to show the values of ICD10Code and Mic.  If the PUT statement does not execute, that means you don't actually have the value T21.06XA in your data (maybe there are leading spaces, or capitalization issues, or... )   data want ; set have ; IF substr(ICD10Code,1,3)="T21" & substr(ICD10Code,8,1) in ("A", "B", "C") THEN MIC=1; IF ICD10Code="T21.06XA" then put ICD10Code= Mic= ; run ; And of course it always helps to look at your data:  proc freq data=have ; tables ICD10Code*Mic/missing list ; format _all_ ; run ; The IF statement you posted works, so it's likely that the value of ICD10Code is not matching your expectation.   1 data have ; 2 ICD10Code="T21.06XA" ; 3 run ; NOTE: The data set WORK.HAVE has 1 observations and 1 variables. 4 5 data want ; 6 set have ; 7 IF substr(ICD10Code,1,3)="T21" & substr(ICD10Code,8,1) in ("A", "B", "C") THEN MIC=1; 8 IF ICD10Code="T21.06XA" then put ICD10Code= Mic= ; 9 run ; ICD10Code=T21.06XA MIC=1 NOTE: There were 1 observations read from the data set WORK.HAVE. NOTE: The data set WORK.WANT has 1 observations and 2 variables. ... View more

Thanks @Ksharp .     I didn't think your code would work because calculatedDate stores a single quoted string with calls to %sysfunc: 1 %let mm=09; 2 %let yyyy=2025; 3 %let calculatedDate ='%sysfunc(INTNX(MONTH, %sysfunc(MDY(&mm., 1, &yyyy.)),0,e),yymmdd10.)'; 4 %put &=calculatedDate; CALCULATEDDATE='%sysfunc(INTNX(MONTH, %sysfunc(MDY(&mm., 1, &yyyy.)),0,e),yymmdd10.)' I guess I forgot (or never knew?) that %bquote will resolve macro triggers inside a single quoted string. 5 %put %bquote(&calculatedDate); '2025-09-30'     ... View more