About LaurieF

LaurieF · ‎05-25-2019

I'll play around with the multiplier option. Thank you. As for the second point - I think you missed what I said. Hadoop is weird, and it's weird because you can't update data. I know that. And since the design, which I have no say over, already calls for SCD2 tables in Hadoop, I had no choice but to write transformations to handle updating existing tables with changing source data. What you're suggesting would make an already complicated process even worse.

LaurieF · ‎05-20-2019

The Oracle source schema is defined as utf-8 The SAS Oracle libname to the schema has encoding="utf-8" on it SAS is running with utf-8 encoding Hadoop naturally runs with utf-8 Because Hadoop is weird, I've got user-written transformations to do SCD-2 processing Because Hadoop is blindingly fast, rebuilding the target table from scratch appears so far to be an extremely efficient way of moving data around. But Hadoop is weird. When I register the metadata to an Oracle table, it shows the column length for character variables as four times the varchar2 length - to support the maximum length a variable can take. The Hadoop table registration shows the (utf-8) character length. Assume a varchar2(512) in Oracle going to varchar(512) in Hadoop (initially in Hive, but later processed in Impala/Parquet). The registration for that column shows it as having a length of 2048. Shrug - I don't care in this case, because I know there are no special characters. It becomes an issue later with other source data that I don't have any control over. I need to see the data to check what's going on in that case. But not today. When I do a bog-standard SAS/SQL insert of it into the Hive table, for this status_message column I'm getting: WARNING: Character expression will be truncated when assigned to character column status_message. This is a message I'd expect to get with catx in SQL where the variable length exceeds 200 (BTW, SAS, can you fix this?!). But I appreciate in this case that it sees a length of 2048 which it's trying to squeeze into 512, and SAS semi-helpfully wants me to know about it. What to do? what to do? I can make the target table have the exploded lengths, but that's cheating. I don't want to have to cheat. I want to be able to avoid all warning messages if I can, and obviously preserve data integrity. What happens when I do have UTF-8 characters, like Te Reo Māori macrons, or emoji characters (apparently, there will be some!)? Laurie

LaurieF · ‎04-22-2019

External commands are rarely, if ever, permitted at virtually all the sites I've worked at. Since I wrote that macro particularly to deal with loading spreadsheets from within SAS/DI, the LSF userid was locked down even more, so there was no way it would be allowed. On top of that, it was a Linux site...

LaurieF · ‎03-27-2019

I'm writing a user-written transformation for a whole series of DI jobs. I can't get all the metadata I need from the macro variables created for source table, so I did what I normally do: read dictionary.columns. And it worked fine - until one of the tables came from SQL Server with a non-standard name: TEST_TABLE$. This is represented as "TEST_TABLE$"n, and when I pass that into my query, it gets resolved as ""TEST_TABLE$"n", which won't work... What to do? What to do? Hmm - how about this? %let _target_name = "TEST_TABLE$"n; data _null_; retain &_target_name ' '; /* Prevent warning message */ call symput('meta_target_name', strip(vname(&_target_name))); /* vname strips the trailing 'n' and quotes */ run; %put &=meta_target_name; And it returns: META_TARGET_NAME=TEST_TABLE$ I've only ever used vname and its friends for walking over an array of columns, and never for a single column. But in this case it did exactly what I wanted, and I was able to pass "&meta_target_name" directly into my query. So much easier than much more complicated ways of stripping of the trailing n and dequoting (or unquoting; I can never remember) the result. I think I deserve a beer. As does the inventor of vname. See also VNAME function documentation Other variable information functions in SAS - see the "Variable Information" category in this list.

LaurieF · ‎02-18-2019

As I've pointed out before, there's only one variable I was concerned with, and that was the underscore one. I've been working at this site for a year, and I've constantly been struck by the lack of facility in reporting analysts who have been called on to do ETL work for which they are not equipped. Occam's razor - you're overcomplicating things. It's lack of skill, backed up by lack of training, backed up by getting the wrong person to do the job. PS You don't want me doing maths…

LaurieF · ‎02-17-2019

No, you've missed the horrible bit - there's spaces either side of the underscore. _ is a valid SAS variable name.

LaurieF · ‎02-17-2019

I expect you're ascribing intention to incompetence.

LaurieF · ‎02-17-2019

You're very kind. I'm going for incompetence.

LaurieF · ‎02-17-2019

Actually I know what it does, and I can guess what the intention was as well, but these variables are used throughout a whole process. Sadly, infuriatingly, it is quite hard to untangle. Hold on to your hats: id_fda= cats(of id _ year_fda); id_lda= cats(of id _ year_lda); As a hint, id, year_fda and year_lda are defined variables from the previous step. But the other variable, ahem, _, is not. The cat family of functions is quite good at casting numeric variables to character, without warning messages and internal generation of inefficient code. As long as it knows about the variables from the dataset vector. When that variable (ahem - _) is not defined it gets a little upset. The intention was, we believe, to strip and concatenate the id and year_xxx variables together, with a separator of '_'. If _ was defined as character with the contents of '_', it would have worked. But instead (I feel unclean) its referenced as a numeric variable with a missing value. This gets cast from numeric to character so that we now have id and year_xxx stripped and concatenated with a #$>?@#$ '.' in between. Someone get me a beer, please. I've seen some rubbish in the last forty years of SAS programming, but this takes the cake.

LaurieF · ‎01-23-2019

This produces what you want: Data Have; Length ID 8 DateCharacter $ 6 date 4 period 3 table_name $ 19; format date ddmmyy10.; Infile Datalines Missover; Input ID DateCharacter; period = inputn(substr(datecharacter, 5, 2), '2.'); date = mdy(1, 1, inputn(substr(datecharacter, 1, 4), '4.')); table_name = putn(date, 'date9.') || '_' || putn(intnx('month', date, 11, 'b'), 'date9.'); keep id datecharacter period table_name; Datalines; 1 201601 1 201602 1 201603 1 201604 1 201605 1 201606 1 201607 1 201608 1 201609 1 201610 1 201611 1 201612 2 201601 2 201602 2 201603 2 201604 2 201605 2 201606 2 201607 2 201608 2 201609 2 201610 2 201611 2 201612 ; RUN; The only complex bit is the creation of the tablename variable. table_name = putn(date, 'date9.') || '_' || putn(intnx('month', date, 11, 'b'), 'date9.'); The intnx function advances the date variable (which contains 1janyyyy) by 11 months to 1decyyyy - the 'b' parameter (which is the default) makes it the first day of the period (in this case month). is that all you need?

LaurieF · ‎01-15-2019

If I interpret what you're saying correctly: yes indeed. Have a look at this: http://www2.sas.com/proceedings/sugi23/Coders/p75.pdf

LaurieF · ‎12-12-2018

That's bloody excellent. Thank you. I've come to the same, or similar, conclusion from a slightly different direction: a DI job should do one thing (have one target), and that makes it easy to put into an LSF stream. LSF can then do its magic by parallelising as much as possible, and we can all go off to the pub.

LaurieF · ‎12-03-2018

The eternal mystery that is SAS macro quoting. Why? I don't really know. I've never had much success with the escape characters, but I do know that do use the percent sign in this context, you need %nrstr. %macro test; %let teststr = %nrstr(a%*b%*c); %put %scan(&teststr, 2, %str(*)); %mend test; %test; You can use %qscan, or %scan - it makes little difference.

LaurieF · ‎12-03-2018

The comparison bit is easy: catx(' ', street_number, street_name) = address_2. What's hard, but not hugely, is to find the street-type identifier (street, road, boulevard, grove, lane, alley; usually the last word) and standardise it on both sides. My method is to abbreviate it in both places (street -> st, grove -> gv) and strip out all punctuation, including commas and full-stops. I'd also recommend setting them both to upper or lower case. I had initial problems when I was working in England with a town called Street, but because the meaning of the words isn't important, the match worked perfectly.

LaurieF · ‎12-02-2018

You're almost there, but you've got it the wrong way around. data new; set input; if '1jul2015'd <= entry_date <= '30jun2016'd then output; run; Note to keep the date range, you want to output, not delete (note that delete removes observations from the output, not the input!) data new; set input; if '1jul2015'd <= entry_date <= '30jun2016'd; run; When you've only got one output dataset, the output action is implied.

Online Status	Offline
Date Last Visited	‎10-28-2024 09:00 PM