About DaveBirch

DaveBirch · ‎05-10-2021

I've found that occasionally when promoting changes of Repository Table definitions from the Development to other environments (e.g. Test and Production) DI Studio makes mistakes and keep some of the old columns that been removed or renamed. If these mistakes are detected and corrected then promoted jobs can generate incorrect code that always produces errors or warnings. Using Proc Contents (or querying Dictionary tables) I can see what the actual columns in a table are, and manually correct the mistakes. What I would like though is a way to (semi-) automatically compare the metadata table definitions with the actual structures found in the database. Has anybody done this kind of comparison?

DaveBirch · ‎03-15-2018

P.S. I'm assuming that perm.have is ordered or indexed by replicate id pdate.

DaveBirch · ‎03-15-2018

Hi @goldenone, Although @ballardw gave sound advice that "often data step merge is not the best for many-to-many merge operations" the problem isn't that there is different numbers of repeating values on each side of the merge. Indeed, being a self-merge (or self-join) you have exactly the same number of rows on each side! I'm quite confident that perm.havenew would be exactly the same if you removed the by statement. Ponder that for a moment 🙂 Data steps usually process data sequentially, so a merge statement will not lead to comparing different rows in a self-merge. Try something like this (untested code): data perm.havenew; retain _RowNum_ 0; do until last.id; set perm.have (in=a drop= &droplist) nobs=NumObs; by replicate id /* pdate */; _RowNum_++1; if first.id then StartIdObs = _RowNum_; b_pdate = pdate; ** to force the while condition (below) to true **; do _i_ = StartIdObs to NumObs while ((pdate - b_pdate) ge 0); set perm.have (in=b rename=(pdate=b_pdate)); if (pdate - b_pdate) ge 0 and (pdate - b_pdate) le 6 then output; ** or maybe cash_wd cash_wdtri dtotal dtotaltri ** ** are accumulated before an obs is output? **; end; /* of do _i_ */ end; /* of until last.id */ run; P.S. it would help if you tried stating your requirements in a way that was independent of the solution you had first thought of. Hope this helps.

DaveBirch · ‎03-14-2018

Essentially this is about matching values within (a) string(s) in a table to an external set of values. Doing this with SQL joins can become very messy and expensive with more complex cases than this example. Fortunately, it is easy to do with a datastep. Two main approaches involve first loading the list of values in table A into memory - either an array or a hash table. Then either iterating over the values in the search list and to see whether they're found in the string, or iterating over the 'words' in the string to see whether they're in the search list. Here is one solution (untested). proc sql noprint; select count(*) into :NumA from work.a; quit; data work.c; array srchval{&NumA} $ 10; ** note: arraysize defined at compile time **; if _n_ = 1 then do; do Aptr = 1 to NumA; set work.a() nobs=NumA point=Aptr; srchval{Aptr} = Sponsor; end; end; set work.b; do _i_ = 1 to NumA; if find(collabolator,srchval{_i_}) then Sponsor = srchval{_i_}; end; run;

DaveBirch · ‎12-21-2017

So it looks like modate is the offending variable. My guess is that the operating system or file system is passing some value like 00/00/00 to SAS. Further, I'd guess that is either the TERMINAL or the PIPE that is the offending external file. Try seeing whether WHERE XENGINE NOT IN('TERMINAL','PIPE') gets rid of the problem. You may need decide which variables/rows you really need.

DaveBirch · ‎12-17-2017

To use a Teradata Stored Procedure (or any DBMS Stored Procedure) you need to do it as a CALL EXECUTE, and the results would be a Teradata temporary table (or Global Temporary table, or some kind of scratch table). You would then use Create table work.sasresult as Select * from connection ... to read that table into SAS.

DaveBirch · ‎12-14-2017

Assuming that an ID can't have two instances of DX=0, you could try: data work.want; set work.have; by ID; if DX eq 0 then do; if (first.ID and last.ID) then delete; else if (first.ID and not last.ID) then delete; end; run;

DaveBirch · ‎12-14-2017

It is worth noting Chris Hemedinger's use of the colon name modifier here. It may be that instead of specifying all 42 expected datasets, that the following SET statement would suffice: SET work.monday_: work.tuesday_: wordk.wednesday_: work.thursday_: work.friday_: work.saturday_: work.sunday_: ; Providing you had at least one dataset for each day of the week, and no others that weren't required in the concatenating SET, this would work. Personally I'd recommend either changing your regressions to use BY processing, or building a macro variable for the dataset list and using the EXIST function to determine whether a name is added to the list.

DaveBirch · ‎12-11-2017

A couple of questions: 1) do you need to handle instances of multiple transactions on the same day? and 2) how do you want to handle instances where transactions occur on 4 or more consecutive days. Intuitively, a self-join would be better than any form of counter, or alternatively, multi-pass data step solutions.

DaveBirch · ‎12-11-2017

You could try something like this: proc sort data=offenders; by id offence_date; run; data offenders1; set offenders; by id offence_date; prev_offence_date = lag(offence_date); if first.id then delete; /* assuming only interested in offenders with multiple offences */ /* otherwise we may need the following line and others */ /*if first.id then prev_offence_date = .;*/ run; proc sort data=hospital; by id admit_date; run; /* SQL handles M:N instances better than data step MERGE */ proc sql noprint; create table want1 as select id, prev_offence_date, offence_date, admit_date, sex from offenders1 t1 left join hospital t2 on (t1.id eq t2.id) where (t1.prev_offence_date le t2.admit_date lt t1.offence_date) order by t1.id, t1.offence_date, t2.admit_date; quit; You now have a dataset with a row for every hospital admission that occurs "between" offences for offenders. This is in many ways easier to analyse (and more flexible) than a more "flattened" structure. If you do desire to flatten it, there are many business issues still to be resolved to determine the best approach. Cheers

DaveBirch · ‎12-11-2017

I am also quite mystified as to what you're aiming to achieve with the statement: where i=1 to end(admit_date); The variable i doesn't appear in either of your input datasets. My guess is you're trying to analyse the correspondence between offence_date and offence_date using array processing, but don't have an array statement. In any case, array are not good at handling highly variable data (you need to define a fixed number of array elements in the datastep). It is a good idea when asking for technical assistance in a forum like this, to step back and describe your problem in business terms rather than in technical details of the approach you're first attempting. This allows others to offer solutions that may be better able to solve your underlying problem. If you are trying to analyse the correspondence between offence_date and offence_date, then I'd suggest renaming, or duplicating the date variables (to event_date ?), merging by id event_date, and maybe using lag functions to assist in identifying the correspondence between offence and hospital admission. Hope this helps.

DaveBirch · ‎06-07-2016

Note that SAS/SHARE is a standard component of SAS Enterprise Business Intelligence (EBI), though mainly transparent to most users.

DaveBirch · ‎06-07-2016

Hi Matthijs, it's not clear what you mean by "SAS Excel" - I thought perhaps you were referring to the SAS Add-in for Microsoft Office (AMO) being run from Excel, but it's clear that the other responders weren't contemplating that at all. Does your site have SAS AMO? It is always (I think) bundled with SAS Office Analytics (OA) and SAS Enterprise Business Intelligence (EBI), and optionally with some other bundles. It is normally site licenced, rather than per user. And, most importantly, it can do exactly what you want ! - All whilst retaining the familar look and feel for users (including cell formating without complex coding in SAS), and eliminating most needs for Excel formulas and macros. AMO has had 'write-back' capability for some time, but only since SAS 9.3 and AMO 5.1 has it been what I consider 'production ready'. In AMO 7.1 the SAS ribbon has an 'External Data' section with tabs for 'Begin Edit', 'Commit', 'Create New Records', and 'Delete Records' etc.. When a user clicks on 'Begin Edit' the underlying SAS dataset is locked, and the tab changes to 'End Edit' (which will release the lock). I recommend that the first Stored Process creates a temporary table to edit, and a second Stored Process validates the altered table before applying the changes to a permanent dataset. You would need to register the users in SAS metadata, but since it is site licenced, this won't involve extra cost. Does this sound like a better solution?

DaveBirch · ‎06-04-2016

I'm trying to insert data a named range in an .xlsx file with the following code: libname MyXLSX PCFILES path="&SrcPath.\Alt_Rptg_Tree.xlsx" SCAN_TEXTSIZE=NO; PROC SQL ; INSERT INTO MyXLSX.Acct_Chkr SELECT t1.Class_Description, t1.Sub_Class_Description, t1.Item_Description, t1.Cost_Element_Num, t1.Cost_Element_Description, t1.Rptg_SubClass_Name, t1.Rptg_SC_Order FROM PERM.REF_CE_TREE t1; QUIT; But, despite having the column attributes set to number in Excel (with Format Cells…), I get the following error: ERROR: Value 4 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name. When I view the properties of MyXLSX.Acct_Chkr they are all defined as char with $255 format. Is there any way either in SAS or Excel to force the columns to match the type in my data? (Using SAS9.4 through EG7.1 with a Windows server.)

DaveBirch · ‎12-15-2014

I don't think it is the Bad or unknown email address that is causing your program to terminate the data step - these are only Warnings. The real problem is indicated by "ERROR: No email addresses specified" - i.e. the email process thinks it doesn't have an email address to process, not just a bad email address. I think this may be caused by: if last.mail_id then do; ... put '!EM_NEWMSG!'; end; which I think would initiate an email message without an EM_TO when the final row in the mails dataset is reached. I've often had Bad or unknown email addresses, and they've never caused my SAS programs to crash. But, in my case, the email process has always been asynchronous - usually through SMTP.

Online Status	Offline
Date Last Visited	‎05-10-2021 09:09 PM

How to compare Metadata Repository Table information with Proc Content...

Re: data merge using date range

Re: data merge using date range

Re: Find and populate

Re: PC error for sas view sashelp.vextfl

Re: Unable to Call Stored Procedure from Teradata to SAS

Re: Keeping observations by longidutinal data techniques

Re: Ignore missing table when setting multiple tables in data set

Re: How to select rows having transactions on three consecutive days

Re: Transpose then merge the two data sets

Re: Create Macro variable from array

Re: Create table to meet certain conditions

Re: Every month update an excel file with new monthly information

Re: Ignore missing table when setting multiple tables in data set

Re: Ignore missing table when setting multiple tables in data set

How to compare Metadata Repository Table information with Proc Content...

Re: Comparing a variable from one record to the previous record

Re: data merge using date range

Re: PC error for sas view sashelp.vextfl

Re: Transpose then merge the two data sets

How to compare Metadata Repository Table information with Proc Content...

Re: data merge using date range

Re: data merge using date range

Re: Find and populate

Re: PC error for sas view sashelp.vextfl

Re: Unable to Call Stored Procedure from Teradata to SAS

Re: Keeping observations by longidutinal data techniques

Re: Ignore missing table when setting multiple tables in data set

Re: How to select rows having transactions on three consecutive days

Re: Transpose then merge the two data sets

Re: Transpose then merge the two data sets

Re: "Alter Table" via SAS Excel?

Re: "Alter Table" via SAS Excel?

Inserting into a named range in an XLSX file

Re: sending emails from sas