About LaurieF

LaurieF · ‎06-26-2023

I've been SASsplained! What an ignorant and patronising reply. And wrong. I've been using SAS for 45 years, and DI for twenty. I know what it can do. I know it can be used, at least as well as anybody else. Now if you aren't going to explain to me how I can use a facility built into a SAS/DI job, which doesn't appear to me to work, I suggest you delete what you just wrote and go and SASsplain to someone else.

LaurieF · ‎06-25-2023

A loop wouldn't be appropriate - each file has different metadata which the transformation acts upon. Remember the issue is the name of the json file, not the entries within it. When I open and run the job from within DI, I want to select the JSON file, as I would if I were running it from just a transformation. From a transformation I open it, click on Code options and select the file I want. From the job, I have to open the parameter, edit it and change the default to the file I want. There's no Code options at job level.

LaurieF · ‎06-25-2023

My latest DI transformation takes a JSON file as input, but there are potentially multiple files to read, from which I'm extracting just one dataset. The size of the file doesn't matter, because the JSON engine is very quick at reading even a multi-million observation file. For ease of management, I'm calling the transformation multiple times in a job, with the output work dataset from the transformation being the JSON entry. It works, and works very well. I previously had the JSON file as File or directory (input) option in the transformation, but with multiple transformation calls in a single job (I have reasons) I have put that file reference in as a job parameter. And that works fine. What doesn't is that I don't seem to. be able to reference that parameter from the job - I have to go into the parameter and change it instead of having it as a prompt that I can easily edit. Interestingly when I set up the parameter, I can use Test Prompts… in the same way as I would a transformation option. I just don't seem to be able to reference it any other way. Is there something obvious I have missed?

LaurieF · ‎09-12-2022

My user-written SAS Data Integration Studio code has to be able handle anything that gets thrown at it. And because I have no control over how the source or target tables have been defined, that includes source tables that have "Enable special characters within table or column object names" turned on. There's never any reason for this (as most source systems won't have column names with spaces or special characters), and it's probably because the developer who created the metadata for the table had their options set up poorly. Nevertheless the metadata has been constructed like that, and I have to cope with it. So I end up with a table name in the job defined in DI as: %let _source = srcref."accountType"n; When I compare that table against the target's target.acccounttype, even accounting for case, it fails because it's not expecting the quotes and n suffix. I thought I'd patched the code to account for all of these, but another one cropped up this morning with a new macro where I'd forgotten to include this little beauty: %let table = %sysfunc(dequote(%sysfunc(scan(&&&table, 2, %str(.))))); where table contains _source. The dequote function simply strips quotes off strings, but what I infer it's doing is taking off the first quote, walking along the string until it finds the last quote and stopping. So the result in table is simply: accountType This I can use, and the code no longer throws errors. Previously I'd created a DATA step where I used vname function to do the same thing (another handy side-effect), but that isn't available in %sysfunc. From the time the process specialist told me we had lots of jobs which had failed, until I'd changed the code, tested and pushed it for testing: 15 minutes.

LaurieF · ‎05-31-2021

I've been trying to get around an issue when I upload data through SAS to an ODBC-connected DBMS. I've tried all sorts of variations, but the issue won't go away. Well, I say issue, but I don't know what the issue is! I'm creating a table via an SQL execute statement, then using a SAS-initiated insert statement to load into it. The message is: NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources. Despite this, the dataset it replicated on the other end apparently perfectly without loss at the observation or variable level. sqlxrc is 0; sqlxmsg is null; sqlobs shows the correct number. I'm mostly convinced that it is something to do with numeric, non-datetime variables, perhaps related to where those variables contain null values. But I can't pin it down to just one variable on one row. I've seen references to this note a couple of times elsewhere, but the explanations for it don't appear to be relevant to what I'm doing. Any ideas?

LaurieF · ‎04-28-2021

Getting a car to replace the horse and buggy would be a bit of A Mishion… And it worked. Genius. I've been using connect clauses for 25 years, and I didn't know about that syntax. I owe you a beer, when you're allowed to enter Aotearoa.

LaurieF · ‎04-28-2021

I've done it without the where clause, and that doesn't help. It provides Schema, Server Version and Client Version only. I'm using SASIOSNF, which I think is either ODBC or JDBC. And remember I'm trying to programatically generate the sql connect clause, so that I am able to then execute pass-through code - cart before the horse, sadly.

LaurieF · ‎04-28-2021

I'm trying in DI user-written transformations to automagically generate SQL connect clauses from libname statements. In other DBMSs, I pick out the schema information from dictionary.libnames (where sysname like 'Schema%'). This works for Snowflake too, but there's no row in libnames for anything like sysname like 'Database%'. When I productionise this code, the schema name is likely to stay the same but the database name will change, and so I want the process to be dynamic. Has anyone had any experience doing this?

LaurieF · ‎09-09-2020

It's a pleasure. It can be tricky mapping the id columns with the transpose transformation. I find that, if I don't already know the cardinality of type, I run the job, refresh the metadata and see what's what. It all depends on what you want to do with the data afterwards.

LaurieF · ‎09-08-2020

The SAS method for pivot tabling is proc transpose, and under the Data Transformations you'll see the Transpose transformation. Here's Base code that will (mostly) do what you want: data from; infile cards firstobs=2; attrib id length=3; attrib type length=$ 7; input id type; cards; ID TYPE 1 NEW 1 NEW 1 CHURN 2 NEW 2 CHURN 3 WINBACK ; run; proc sql; create table to as select id, type, count(*) as count from from group by id, type; quit; proc transpose data=to out=to_trans(drop=_name_); by id; id type; var count; run; option missing='0'; proc print data=to_trans noobs width=min; run; Note that I setting missing to '0' at the end. This is because there are obviously values in the transposed dataset which don't have a value; proc print would otherwise print them as missing. If you want them as zero in the dataset, you'll have to do some post-processing. So as you can see, you'll have to process your dataset twice: once to do the counts (SQL Extract transformation), then rotating it (Data Transpose).

LaurieF · ‎09-07-2020

I've been puzzling over this post (Case-statement-trims-length-to-200) by @ChrisNZ - and the weirdness continues. Before I found Chris's extensive analysis of what was going on with his problem, I managed to get a Perl expression to work at more than 200 bytes. But I had to jump through hoops to do it. And I still don't know quite what's going on. A few of our source files have 32k-byte HTML text in them, which requires decoding and tag stripping. Not only that, but the often contain tabs, carriage returns and the like - all of which are a barrier for textual analysis. My original code was strip(compbl(translate(prxchange("s/<.*?>/ /", -1, htmldecode(htmldecode(message))), ' ', '08090a0b0c0d'x))) as message length=32767 It was unwieldy, but it worked. (htmldecode is called twice, because sometimes that's the only way of getting rid of the special characters - I don't control the source data!) And then I looked at the output and saw that no value was longer than 200 bytes - often less because of utf-8. But cutting out one function at a time, I found that prxchange was working as expected. So I created an extra work column and added one more function at a time, and it still worked. Here's what I've ended up with: prxchange("s/<.*?>/ /", -1, htmldecode(htmldecode(message))) as untagged_message length=32767, strip(compbl(translate(calculated untagged_message, ' ', '08090a0b0c0d'x))) as message length=32767 /* six spaces between the quotes */ I have no idea why splitting it into two expressions and columns works. Does anybody have any suggestions?

LaurieF · ‎01-27-2020

Ah - that's not the actual answer, but it's the hint I needed. The job being deployed in production (this is from DI) would appear to be using a different set of parameters to generate the sasbatch.sh call. Thank you, Laurie

LaurieF · ‎01-27-2020

I don't get it. I have jobs running in batch in development and test which do what I expect. On rare occasions, the job will detect an error and throw the information about the problem to the same name as the job's log file, with the suffix changed from .log to .lst. For example DM_Daily_Batch7_table_name_P2H1_2020.01.20_03.05.50.log will be paired with DM_Daily_Batch7_table_name_P2H1_2020.01.20_03.05.50.lst. It's not flash, but it works well and everyone is happy with it. But when we run it in production, the log name, from the option log , is consistently the above truncated immediately before the timestamp: DM_Daily_Batch7_table_name_P2H1_. It doesn't matter how long the name of the log file is - it's always at this point. What sort of weird environmental setting would cause this? and how do we go about changing it?

LaurieF · ‎09-05-2019

There’s no bug in the code. fname is a data step variable, not a macro variable. It works exactly as intended. And you’ve got your copyright attribution on *my* code. That’s a bit **bleep**. Laurie

LaurieF · ‎08-06-2019

Want to add on something really stupid? This is from the days before %sysfunc, putn and the z format... %let j = %substr(000&j, %length(&j), 4); For values of j up to 9999, right-justify and zero-fill j. Want longer numbers? Add zeros, and change the last parameter. I used this construct for years, but the documentation was usually fairly thorough.

Online Status	Offline
Date Last Visited	‎06-29-2023 07:57 PM