About LaurieF

LaurieF · ‎05-03-2018

I don't disagree. I love formats, and have since 1979. But the majority of the time I am working in organisations where I have little or no control over the environment and have to cut my coat to suit the cloth. I've started to move away from formats in a production environment, because as often as not there's not enough source management control over them and there are often multiple versions floating around. That's why we have single repositories of the truth, in databases and warehouses. But they still have their uses. However, as I pointed out above, if you are going to use formats, using this macro can allow you to reformat the code to make it a bit more manageable.

LaurieF · ‎05-02-2018

Hmm (runs off to the hills in fear of multilabel formats) To be honest, they might work, but again they might not!

LaurieF · ‎05-02-2018

Many years ago I worked on an old IBM VM/CMS box which was at end-of-life, but not-quite-yet. The platform manager had a cartoon of two old guys digging in a cemetery with the caption, "I know we buried that old mainframe around here somewhere..." One of the old projects I was working on made extensive use of formats, but the source code of those formats was long lost. Using cntlout in proc format allowed us to edit the formats and republish them, but it frustrated the hell out of me that it was such a laborious process. So I wrote one of the macros I'm proudest of: %genfmt. OK, it's not such a great name, but it does just that - it generates or recreates format code. Over the years I've upgraded it a bit from its humble value beginnings, especially to include invalue (character and numeric) and picture types. Using invalues is interesting - to get the select statement to work, you have to prefix the format name with @. Not many people know that. Usage: %genfmt(library=library, format=format name, ovrwrite=n*, sasgen=, debug=n**) *Because the code writes out a file called format name.fmt, this stops it proceeding if this file already exists. If you don't care, or it doesn't exist, you'll get: INFO: The format generation code is stored in file "$clevel.fmt". in your log. The output code's filename can be replaced by sasgen. **Shows the code generated by the macro in the log As a side effect, I found that it does a pretty good job of compressing format code as well. For example, here's an old format I was working on just this morning: value $clevel '<2B' = '<2' '2B' = '2' '2P' = '2' '2A' = '2' '3B' = '3' '3P' = '3' '3A' = '3' '4B' = '4' '4P' = '4' '4A' = '4' '5B' = '5' '5P' = '5' '5A' = '5' '6B' = '6' '6P' = '6' '6A' = '6' '>6A'= '>6A' ; I applied genfmt to it and this was spat out: value $clevel "2A", "2B", "2P" = "2" "3A", "3B", "3P" = "3" "4A", "4B", "4P" = "4" "5A", "5B", "5P" = "5" "6A", "6B", "6P" = "6" "<2B" = "<2" ">6A" = ">6A"; There's nothing particularly clever in the code, but I have documented it pretty well, I think, so you can see what I've done and how I've done it. Note that it copes with quotes within labels, inclusive/exclusive ranges, other values, imbeded formats and, I think, pretty much everything else. This is, as ever, open source - use it in health, and let me know if you've liked it and especially if you've found any bugs. Payment can be made in beer.

LaurieF · ‎03-13-2018

Related to https://communities.sas.com/t5/SAS-Enterprise-Guide/Changing-run-order-for-programs-in-process-flow/td-p/390959, I have a problem. In a slightly complicated project I have multiple serial processes which, although they could be parallelised, I want to keep separate to stop my head exploding. Yesterday that's how they ran. This morning, seemingly without modification, one of the minor threads has changed direction. Instead of running 1-2-3-4, it's switched to 2-3-4-1. This is not quite what I wanted. Process #1 sets two datasets together (I know I could use the append task, but that's not the solution I want). Previously I had the two sources (manually) linked to this process to show the precedence I wanted. If I de-link the first dataset it reverts to 1-2-3-4, and the whole project runs to completion with nary a warning. Woohoo. But why? PS EG 7.13 (7.100.3.5408) (64-bit)

LaurieF · ‎01-15-2018

Try it like this: data work.sales; infile datalines dlm="/"; input ID First :$12. Last :$12. Gender $ Salary:comma. Title :$25. HireDate :date.; format HireDate mmddyy10.; datalines; 120102/Tom/Zhou/M/108,255/Sales Manager/01Jun1993 120103/Wilson/Dawes/M/87,975/Sales Manager/01Jan1978 120261/Harry/Highpoint/M/243,190/Chief Sales Officer/01Aug1991 121143/Louis/Favaron/M/95,090/Senior Sales Manager/01Jul2001 121144/Renee/Capachietti/F/83,505/Sales Manager/01Nov1995 121145/Dennis/Lansberry/M/84,260/Sales Manager/01Apr1980 ; run; Title "Orion Star Management Team"; proc print data=work.sales; run; Title; When you use 'cards' or 'datelines', it should be the last command in a data step. Your code as it was was reporting an error.

LaurieF · ‎08-29-2017

One of my current tasks is to convert a hand-written EG project into a manageable DI solution: one that can be scheduled and maintained and so on. I came across a weird bit of code: proc sort data=source out=target; by acct_date descending prev_balance; by account_id posting_dt Transfer_Order descending prev_balance; run; Oh - that looks a bit weird. I wonder what it does - does it use the first by statement, the second, or concatenate them together? With a bit of expermentation, I found that it only uses the last one it sees. With the data contents being what they were (don't ask), it made little difference as to which statement was being used, but the last one was preferable. I suspect when the code was written, the second line was put in and the first line left there; it compiled and executed and didn't produce an error, so hey-ho. So then I thought I should experiment. What I came up with is that (not exhaustively): procedures don't care about multiple by statements and use the last; but data steps produce: ERROR 221-185: More than one BY statement specified for a SET, MERGE, UPDATE, or MODIFY statement. So there you go. Novel, of no use whatsoever (I mean, why would you?!), but it afforded a little amusement/bemusement in the office today. It's interesting to infer what's going on under the parser's hood. And back to work.

LaurieF · ‎05-30-2017

I've thrown in a macro there to clean things up, free gratis and for nothing, which may help.

LaurieF · ‎05-30-2017

I just found myself in the same position, so I wrote a macro for it (assuming that file is the name of the spreadsheet without the .xlsx suffix: %macro xlsx_bak_delete(file=) / des='Delete backup spreadsheets'; option mprint notes; data _null_; fname = 'todelete'; rc = filename(fname, "&file..xlsx.bak"); rc = fdelete(fname); rc = filename(fname); run; %mend xlsx_bak_delete; It does no checking whether the file exists, and doesn't put out any fancy messages. It just does what you want. (Obviously, if you're using xls, modify to suit.)

LaurieF · ‎05-22-2017

First point - most other systems I've used use a base-dating system, whether it be 1Jan1960, 1Jan1901 or similar, and allow day arithmetic. Second point - won't happen. All of SAS's date handling would break. Third point - shrug. Except for day multiples ('day.2' et al) (and I've never personally had a reason to use them), I'll keep on using arithmetic, and counselling and training for its use. Today I saw: intck('month', intnx('month', startdte, 0, 'b'), intnx('month', endte, 0, 'b')) instead of intck('month', startdte, endte) It is possible to argue that there may be a reason to use the former, for possible later modification. I would wager a reasonable amount of beer that that time will never come. Make it as simple as possible, but no simpler.

LaurieF · ‎05-22-2017

No, I would of course include it. If I were master of the universe, I'd make the (pseudo-)code have a select for 'day' and use the arithmetic approach - if that isn't how it's done already. But I would also update the documentation to recommend for single day calculations to do it without the functions.

LaurieF · ‎05-22-2017

D--n, that was harsh. And unncessary. Please don't try to teach grandpa how to suck eggs. I am very well aware of these, but that was not the point. It is an edge case. The code I showed was base SAS code (contained within a DI user-written transformation) and it was exactly as I wrote it (on my phone). It wasn't part of a drop-down selection, it wasn't "DAY.2" or anything fancy like that. I accept that these are reasonable responses to my mini-rant that started off, but they are the exception. I come across this usage of intnx and intck in virtually every site I work at, and it annoys me greatly. It makes the maintenance of the code harder, and it also makes it harder to read. It also shows a probable lack of understanding by whoever wrote the code of how SAS dates inherently work. It is of course a requirement of the intxx functions to accept 'day'. It doesn't mean that people should use it.

LaurieF · ‎05-21-2017

Yeah, but that's so much of an edge case it's fallen off the side of the planet. I take your point, but I'm not buying it. I'd much rather keep things simple.

LaurieF · ‎05-21-2017

Can someone explain why anyone would use 'day' as an interval parameter, since day is ipso facto the granular date? Code example: "intnx('day', todaydate, -200)" cf "todaydate - 200". I'm flummoxed.

LaurieF · ‎04-17-2017

Can you provide some sample data? It's much easier to play around with the problem if there's something concrete to work on.

LaurieF · ‎04-10-2017

I've come close, but putting the totals on the first line for a risk_id group means jumping through too many hoops for my proc report skills. I could do it with some tricky data steps, but it starts getting too complicated. data risk; do risk_id = 10, 20; risk_name = ifc(risk_id = 10, 'ID Theft', 'Fraud'); output; end; run; data controls; do control_number = 'c1', 'c2', 'c3', 'c4', 'c5'; risk_id = ifn(control_number le 'c3', 10, 20); output; end; run; data incidents; infile cards dsd dlm=','; attrib incident_no freq amount risk_id length=8; input incident_no freq amount risk_id; cards; 1,1,50,10 2,1,50,10 3,2,50,10 4,5,10,20 5,1,10,20 ; run; data want; merge incidents(in=in_incidents) risk controls; by risk_id; if in_incidents; run; proc report data=want nowd; cols ('Risk Table' risk_id risk_name) ('Control Table' control_number) ('Incident Table' freq amount); define risk_id / group 'Risk ID'; define risk_name / group 'Risk Name'; define control_number / display 'Control'; define freq / sum 'Freq (sum)'; define amount / sum 'Amount (sum)'; break after risk_id / summarize; run; Can you explain the significance of the control number?

Online Status	Offline
Date Last Visited	‎09-02-2025 04:43 PM