You could probably work around the issue of naming convention protection in SAS using DDE. It still involves some minor indirect excel VBA in that you need to know what excel commands allow you to rename a sheet but the SAS naming restrictions wouldn't apply as all would be done within excel. somethin like this - all inserted within a different macro and use end=last set option with if last then call execute('%differentmacro()'); proc sql;      select code      into :y1-:y99999      from g(where=(length(code)=5)); /* You might need to use PRX if you have some codes that are more complicated or more lengthy than 5 characters yet still break naming conventions */ run; %let loopend=&sqlobs; options noxwait noxsync; x '"C:\Program Files\Microsoft Office\Office12\EXCEL.exe"'; data _null_;      z=sleep(5); /* 5seconds sleep, needs to be long enough for excel to open */ run; filename DDEcmds dde "excel|system"; data _null_;      file ddecmds;      put '[open("C:\test.xls")]';      %do i=1 %to &loopend;           put '[workbook.activate("_' "&&y&i" '")]'; *          put '[activesheet.name="' "&&y&i"' "]'; /* this step is still bugging for some reason */      %end;      put '[save()]';      put '[error("false")]'      put '[quit()]'; run; options xwait xsync; *Note this is all untested, I don't use DDE nearly often enough to be confident about the syntax. However, this is an indication of the logic. Vincent *edit* I've been trying to work it out with your sample g data and eventually broke it down into 2 macro to avoid having to clear the libname every step. However, I still can't figure out how to hit the right excel syntax. I've updated the post a bit *edit* I don't know if this is up to date but 4570 - WORKBOOK.NAME command does not work with DDE to rename worksheets inExcel           If that has not been fixed in years, then I can't think of any alternative than using VBA ... View more

Depending on what further processing you want to do and your constraints on how you define the same week from year to year, you can use the week function with by-group processing. Since SQL allows for functions to be applied to variables for the group by statement, you would be able to do a query with group by week(weekcode) Since you use weeku5. format, your dates format use sunday as the first day of the week and so does the default second parameter to the week() function so you won't need to change it. You can't quite circumvent the issue of 53 weeks a year as it's based on the format choice and thus is consistent with itself. You would have to be more precise as to how you'd want to handle the 53rd week for us to help with a custom solution. Week5. format as with all dates formats in SAS are stored as numeric variable. If you wish to query with strings, you need tell SAS to read that string as a date and convert it to the appropriate numeric value e.g. where weekcode="13W29"d; or where weekcode=input("13W29", week5.); You could also ask sas to convert the numeric value to a string with (where put(weekcode, week5.)="13W29") for comparison but I believe it's better practice to do the opposite as then you can apply functions and arithmetic to the dates. If you need further details or examples, please provide the same of your variables and an example of a query you wish to achieve. Vincent ... View more

Hi tmm, There are always a lot of very frustrating situations coming up regardless of what job you do. However, as a friendly advice, you should probably delete your last post and go take a coffee or a beer with a friend to cooldown. I haven't read SAS forums guidelines but I suspect this is out of the line of conduct but far beyond code of conduct is the inherent risk of a co-worker to identify you and the manager you are talking about. As for the risk of losing your job to your upcoming new boss, while you may have started the relationship on the wrong track, if you can control your frustrations, it is often easier to make them change their decision with a calm rationale. In your reply, the way you've approached the whole "tell me where you have duplicates from your query and I will tell you why based on our DD" should be quite eye opening to a less DB knowledgeable manager and with some iterations over time and little teaching, I'm sure he will adopt your way. Vincent ... View more

An even larger concern is that this snippet's loop is running through an entire table to effectively only find the largest _N_ and divide that by 100 to set a macro variable's value thats going to be used further. This is incredibly ineficient. Breaking down a larger job in sets of X records can be worthwhile but the above is not an appropriate way to figure out how many subsets of 100 records you will have to run through. As for crashing your computer going from 100 to 1M records - SAS processes a single record at a time by default as it is built to handle larger jobs than whatever ram you have availible. So unless throughout your code, the breakdown by 100 records is doing some absurd manual merging with hash tables or arrays (both fully run in memory), 1M records shouldn't cause any memory crash issues. Vincent ... View more

Last I read about DDE, it's actually an issue with that the DDE software has to be opened first hence why in most examples you will see a data _null_; z=sleep(3);run; step in most DDE examples online to force SAS to wait 3 seconds (or more) to let excel open prior to sending the first few commands. If you already had such a sleep in your program and still were getting the error from another user having the file open, I'm sorry I can't be of much help beyond that. I can't think of an easy way to verify if an excel file can be open with write priviledges in SAS. Maybe check in excel-VBA documentation for a command to do so and instead of opening the workbook/sheet directly in your DDE statement, simply open excel.exe and use the open() excel command in your series of put statements to open the appropriate workbook/worksheet after using excel commands to verify that it's availible for writing. Vincent ... View more

Hi Given that the date informat and format are different after your import, it is very likely that these dates are already stored as SAS date values but that the format=date9. on your variable turns them into the 01JAN1960 that you are seeing. There are different ways to clear out the output format so that you see the actual days count since 01JAN1960. I would use something like proc datasets library=work nolist /*or wherever else than work your DS is stored*/;      modify datasetname;           format datevar1 8. datevar2 8.; run; quit; ... View more

Hi Tom, If it's not too late, I'd appreciate if you could try a true PRX based approach where PRX isn't used only as a word reader but actually does the work of finding the good/bad words too. I doubt it will compete with other methods on large strings because the .net framework PRX engine is NFA and that's fairly bad for alternation constructs efficiency but anyway - here's how it goes: data good;   input good$;   cards;   wow   great   ok   good   better   best   ;   data bad;   input bad$;   cards;   bad   meh   boring   never   ; data have;   input comment $50.;   cards;   "Wow so great"     "It's OK"            "Good but boring"    "Meh" "Good Good Good Better Best, Never let it rest" ; proc sql noprint;      select good      into :good seperated by '|'      from good;      select bad      into :bad seperated by '|'      from bad; quit; data temp;      if _N_=1 then do;           prxidgood=prxparse("/\b(?:&good.)\b/i");           prxidbad=prxparse("/\b(?:&bad.)\b/i");      end;      set have;      start=1;      goodcount=0;      badcount=0;      do until (pos1=0);      call prxnext(prxidgood, start, -1, comment, pos1, length);      if pos1>0 then goodcount=goodcount+1;      end;      do until (pos2=0);      call prxnext(prxidbad, start, -1, comment, pos2, length);      if post2>0 then badcount=badcount+1;      end;            retain prxidgood prxidbad;      drop start pos1 pos2 prxidgood prxidbad; run; If it's not lagging too far behind, I could try to optimize into a single loop using $1 and $2 regex constructs to try to use a single &good|&bad regex and count according to the replace type. I'd have to read further about PRXNEXT and what can be done however. Thanks! Very interesting thread by the way Vincent *edit updated according to data _null_ 's comment below. It should definitely not be computed each data step iteration. *edit added i option to regex to ignore cases as mentionned by Haikuo below. *edit added the \b...\b to fully delimitate words as PG pointed out. Thanks. However, that is actually one of the strenght of regexes over scans is that you can find words embeded and in different scenarios it may achieve more of the OP's goal. The o option did not appear to work in my testing and sadly, the \b...\b is forcing me to add the parenthesis which means adding a capturing group to the regex and thus significantly decreasing efficiency. To circumvent the effect, I added the ?: at the start of the capturing group...to define it as a non-capturing group. Small scale tests shows its working as intended. I did not know about the o option before as I had done most of the regex self-learning on msdn and the only 5 discussed .net framework options there are imnsx. I can't seem to find what to search SAS help for to get the list of perl options availible. If anyone could point it out that would be much appreciated. I'm stuck with SAS 9.2 still at Statscan I did not request 9.3 yet hoping to jump on 9.4 testing as soon as we get some licenses. ... View more

Since this is the IML subforum, lets assume your data is stored in a matrix named SDATA. Z=0; do i=1 to (nrow(SDATA)-1);      Z=Z+(SDATA[i,1]-SDATA[i+1,1])*(SDATA[i+1,2]-[SDATA[i,2]); end; print Z; a more efficient way would probably be to use 4 temporary matrices of dimension (nrow(SDATA)-1)  and define them like X1=SDATA[1:nrow(SDATA)-1,2], X2=SDATA[2:nrow(SDATA), 2] etc. and use term by term multiplication operator which I forgot on the top of my head as it's been a while since I've used IML. More memory usage but fewer operations. if you want to do this in regular SAS, look for lag<n> function, in your case lag1 function. Vincent ... View more

Pretty sure you have figured that out in 5 days but you simply forgot to read your data INTO DM so proc iml; use kaplan; read all var{time survival survival1} into DM; close; Your DO loop will also crash as you will eventually  try to get time[dim(DM)+1] which is out of bounds. I don't know if it was intended that the time used differed from the survival columns used so I'll let you figure out how you change it, either change SS= and loop from 1 to dim(dm)-1 or change TT= Vincent ... View more

I believe the example below could help you achive your desired results: proc sort data=sashelp.heart;      by sex; run; proc freq data=sashelp.heart;      table weight_status / out=temp2;      by sex; run; data temp2;      set temp2;      array change _CHARACTER_;      do over change;           if change="" then change="Unknown";      end; run; proc gchart data=sashelp.heart;      vbar weight_status/ group=sex sumvar=count; run; quit; The group= option should allow you to achieve your desired result. Thjere are definitely different ways to go about it depending on how much manipulation of your data you want to do before hand versus letting proc gchart do some of it via the statistic options. With multiple variables each having a different scale however, it is probably best to do all calculations beforehand. For instance, I would suggest you run your summary statistics on your table, then use proc transpose to get age/marital_status/income variable vertically within a new variable, use that variable and it's value column as respectively, the hbar variable and the sumvar= variable. If you wanted sex distinction to pile vertically instead of horizontally, you can do so with the subgroup= option. Vincent ... View more

I feel somewhat like an idiot for not having thought of PROC GBARLINE before. However, if anyone reads this and knows a way to have interpol=step work with overlay/areas to achieve the intended output, that would be really appreciated. Vincent ... View more

You may need to provide some code segment to at least help us seize the problem better but based on what you have mentioned, it appears to me as all you need is to change goptions device=java (or goptions device=javaimg) to...well basically any "img" format so bmp/png/jpg even pdf goptions device=pdf; Not all SAS/GRAPH procedures are supported by the java applet or activex control and since you use a pdf output with ods layout anyway, you lose the functionalities they provide when they are converted as javaimg or actximg. Thus, any image format as device= will let you achieve the same results. The drawback is that if you were using the applet/object ATTRIBUTES= and PARAMETERS= options, you will need to trace back how to achieve the same result with SAS ODS options instead as those are obviously not supported by png/bmp/jpg/pdf or any other image device *Edit - when using device=java javaimg activex or actximg for pdf output, the actx control/java applet are first used to generate the graphic and then the graphics generated by the controls are saved as an img format (typically jpg) and that image save is used to generate the odf pdf. If you look in sas help documentation index search GBARLINE and look for "ActiveX and Java support for", you will find that it is supported (most functionalities at least) by the activex device but not by the java device. If you had been using the attributes= and parameters= options, using device=actximg might solve your problem without having to convert all object-based options to sas/graph syntax options. At least assuming that most of their functionalities were developped with the same variables names and whatnot. Vincent ... View more