About Sven111

Sven111 · ‎03-09-2017

I've been using PROC EXPORT with DBMS=XLSX to export data to Excel for a long time, but didn't think formatting could be preserved using this method (as compared with ODS EXCEL, etc). I was updating some code however and noticed that some of the columns were formatted, while others weren't. It turns out that only some of the formatting was being sent across however. Variables formatted with COMMA20. were showing up as comma separated numbers in Excel, while those formatted as DOLLAR20. or PERCENT10.2 were unformatted. Well actually the PERCENT ones were completely unformatted, while the variables with the DOLLAR format didn't have a currency sign or commas, but were hiding the cents columns (with the cents data still being there). Here's an example of the format of an empty dataset I created and was adding data to prior to exporting to Excel: DATA lib_A.TableA; /* CREATE EMPTY NEW DATASET WITH SEVERAL FORMATS DEFINED */ FORMAT Comparison_Group $100.; FORMAT IMPORT_Yr_Mth $50.; FORMAT Eligible_Txn COMMA20.; FORMAT Eligible_Amt DOLLAR20.; STOP; RUN; Which I export to Excel like this: PROC EXPORT DATA=lib_A.Table_A DBMS=XLSX REPLACE LABEL OUTFILE="&output_file"; SHEET="&Sheet_Name"; RUN; And looks like this in Excel: So are only some of the formats sent across using PROC EXPORT? Is there a list of which ones work somewhere? I searched around and didn't find anything obvious, although I did find one presentation on lexjansen.com that seemed to indicate that DOLLAR might pass through as well. If possible I'd really like to be able to send percentage and dollar formatted values across and have them display properly.

Sven111 · ‎03-07-2017

Actually, I just read my own link a bit closer, and it should actually be fairly simple to create a FCMP or Macro function that creates a v3 or v5 UUID/GUID since they primarily rely on MD5/SHA-1, both of which are already existing functions in SAS.

Sven111 · ‎03-07-2017

It sounds like you may want to look into something like a GUID/UUID. I don't think SAS has that functionality built in, but I'm sure you could create some modular code to do it. Or very likely someone already has if you start digging around.

Sven111 · ‎02-23-2017

@Patrick This is somewhat of a tangent from the main topic, but in some cases it is still necessary to explicitly send an EXECUTE(COMMIT) BY AAA. I recently discovered this when I was inserting data into a table while passing the APPEND hint. Without the append hint I could sequentially insert multiple times into the table, but with the APPEND hint, I recieved an error ORA-12840 which states: ORA-12840: cannot access a remote table after parallel/insert direct load txn Cause: Within a transaction, an attempt was made to perform distributed access after a PDML or insert direct statement had been issued. Action: Commit/rollback the PDML transaction first, and then perform the distributed access, or perform the distributed access before the first PDML statement in the transaction. Adding the explicitly passed commit between each insert resolved the problem and allowed multiple inserts. I don't know why this is since I also assumed everything was being committed automatically in each explicit pass-through block.

Sven111 · ‎02-20-2017

Submission file to what? If you're submitting the final output to something outside of SAS anyways (CSV, etc.) then it shouldn't matter how SAS treats it internally as long as you don't need to do numeric manipulation on that variable.

Sven111 · ‎02-20-2017

I was thinking this as well, or even some oddly formatted date/timestamp string (possibly concatenated with another string/value) given that all three examples have 2016 in there.

Sven111 · ‎02-20-2017

Not easily, not that I'm aware of anyways. Here's an article that discusses various options for bignum handling. When I've needed to use larger numbers I usually offload it to a database like Oracle or Teradata and then do explicit pass-through PROC SQL to handle it.

Sven111 · ‎02-16-2017

I've had lots of problems with Oracle-SAS data type as well, although I'm usually going the other direction, from Oracle to SAS, and date/time variables are always the trickiest. One thing I've noticed that may be helpful is that there seems to be a big difference in the interchange between Oracle and SAS depending on if the variable is a DATE, TIME or DATETIME in SAS or if it's a DATE, TIMESTAMP or TIMESTAMP WITH TIME ZONE in Oracle. It looks like below it's using a SAS DATE format, but if it's not too much of a hassle it may be worth converting it to a DATETIME and see what Oracle makes of it on the upload. One other thing to consider, if on upload the SAS DATE variables are DATE or TIMESTAMP values rather than a VARCHAR2, is that you may be able to change the schema parameters in Oracle to alter the default DATE/TIMESTAMP format it displays. I don't recall how to do this off the top of my head, but I don't think it should be too hard to find.

Sven111 · ‎02-16-2017

I think the problem is in your variable names in the APMD.Products table. One of the columns starts with a number, which Oracle doesn't allow. Oracle column names generally have to start with an alpha character (or possibly an underscore, I don't recall). Try the code @PoojaP1 recommended after changing the column name from "95%" to something starting with an alpha character (and possibly removing the % sign as well.) You also may want to review this document on the Oracle Libname engine, I've found it useful as I deal with SAS-Oracle stuff on a regular basis. In particular, making sure you're using some of the SASTRACE options is very helpful in debugging stuff.

Sven111 · ‎02-14-2017

What do you mean by function? SAS specific functions? My first thought would be that you could do this with a CASE statement if that is allowable and you knew all of the swaps that need to happen ahead of time. I don't think you could solve this with just SELECTs and JOINs, although I'd love to be proved wrong.

Sven111 · ‎02-13-2017

I always like seeing a DATA step approach for problems like this since SQL is more of my strong suit and always my first take on a solution. Seeing the same problem solved using both helps me see the problem from a different perspective and learn a bit more.

Sven111 · ‎02-13-2017

That would work too, it's largely a matter of preference on small datasets. On bigger datasets there may be some performance implications for choosing one way or another, at least when passing through to database, I'm not as experienced at pure PROC SQL performance tuning as I am at pass-through stuff.

Sven111 · ‎02-13-2017

Assuming there are two separate tables, one for the Exposure date and one for the Adverse effects, you could create the fifth column like this: PROC SQL; SELECT tbl_A.ID ,tbl_A.AE_Date ,(SELECT MAX(tbl_B.EX_Date) FROM tbl_Exposure AS tbl_B WHERE tbl_A.ID = tbl_B.ID AND tbl_A.AE_Date >= tbl_B.Exp_Date) FROM tbl_AdvEff AS tbl_A; ;QUIT;

Sven111 · ‎02-09-2017

I don't know if it's helpful or not, but another way to go about this would be to just use a hash function (SHA1, MD5, etc) on the datasets and see which ones have the same hash value. This could be coded in SAS using the X command or using a shell script or from the terminal. This assumes the datasets are identical other than the name of the file though and that your in a Linux environment (it's possibly in windows too I assume, but not as straightforward).

Sven111 · ‎02-09-2017

Thanks LaurieF, I was actually already using compression using the CHAR type specified in my SAS autoexec file, but I'll try it out with BINARY to see if that changes anything. Unfortunately reducing the columns isn't feasible in most of my use cases. The primary use case I have in mind (or at least the one that got me started on this path) is test file #7, which only has 3 columns to start with and I can't incrementally restrict the length of any of the columns since the whole point of the file is that it contains frequency tables for various VARCHAR columns of another table so we can see what the distribution of values are for the different column. (Column 1 = Other tables column name, Column 2 = Values found in that column, Column 3 = Number of times that value shows up in the table.) One thing I've been considering, but haven't tested yet is to see if different methods of transferring the data would make a difference. For example is using PROC SQL with implicit pass-through faster/slower than doing the same thing with explicit pass-through or using a DATA step. My gut feeling is that this wouldn't make a difference, but it's probably worth confirming.

Re: Is there any way to disconnect the pre-assigned oracle libraries w...

Re: Oracle ODBC driver

Re: Ask the experts / comment on column ...is

Re: Ask the experts / comment on column ...is

Re: Ask the experts / comment on column ...is

Re: How to encrypt password on log?

Re: How to encrypt password on log?

Re: Oracle passthrough syntax frustration

Re: How SAS output the special charater in string (from Oracle server)...

Re: Find users who are pulling in large datasets from Oracle to SAS

Re: ERROR: Ambiguous reference, column wing_ is in more than one table...

Re: Increase variable name length from 32 to 128 characters

Re: Increase variable name length from 32 to 128 characters

Re: Find users who are pulling in large datasets from Oracle to SAS

Update DBMS tables faster from SAS

Re: Appending a sheet to an already existing Excel document with ODS

Re: How to force updates to SAS Log?

Re: How to encrypt password on log?

Re: How SAS output the special charater in string (from Oracle server)...

Re: Insert SAS table values into Oracle table

Preserving Formatting in Excel via PROC EXPORT

Re: Building a lookup table to avoid duplicates

Re: Building a lookup table to avoid duplicates

Re: XML validation using PROC SQL in SAS

Re: length of numeric variables in SAS under windows

Re: length of numeric variables in SAS under windows

Re: length of numeric variables in SAS under windows

Re: PROC SQL Oracle Table Build - Date formatting to ddmmyys10. ???

Re: SAS to Oracle dataset load

Re: Swap values using PROC SQL

Re: find the last exposure date before adverse event

Re: find the last exposure date before adverse event

Re: find the last exposure date before adverse event

Re: Delete Dataset if a Duplicate Exists

Re: Optimizing Moving Data Between Oracle and SAS