odbc

[Patrick]

---

@HeatherNewton wrote:

yes I only pass to user to execute

no particular sas admin in this place

may be they think I am now

Thank you Patrick for your kind support!

---

Then have them run the code I just shared and come back with the result.

If I was in your situation then I'd also have a chat with my manager and ask for guidance how to proceed as without environment access and from the looks of it no sufficiently skilled partner on the client side it's going to be really tough to investigate and resolve this. You certainly want your managers approval before you continue spending more time on this.

[HeatherNewton]

connect in the second line show up in red, does that means my user'sas sas do not have this function?

[Patrick]

---

@HeatherNewton wrote:

connect in the second line show up in red, does that means my user'sas sas do not have this function?

---

The connect syntax is certainly valid in your clients environment. 

That's how it looks in my EG version.

If syntax highlighting shows it in red then you've got either some syntax error or (not sure) an older EG version than I do where syntax highlighting is just not perfect.

[HeatherNewton]

yes it is working

pls see pic showing result

[Patrick]

So now finally you could share some actual facts that prove:

1. The values on the DB side have a 4 decimals precision

2. The values on the SAS side are really stored internally with only 2 decimals (=not only a display issue).

This is either a bug with the SAS Access to ODBC engine (which I assume it's not) or with the ODBC driver. My assumption is that a driver gets used in a version that's not documented as supported by SAS. 

I feel the only thing you can now do is to advice your customer to raise a track with SAS Tech Support and then take their guidance for further investigation. My advice is for the customer to communicate directly with SAS Tech Support as I can't see how you could add any value just acting as the middle man.

[Patrick]

---

@Patrick wrote:

So now finally you could share some actual facts that prove:

1. The values on the DB side have a 4 decimals precision

2. The values on the SAS side are really stored internally with only 2 decimals (=not only a display issue).

This is either a bug with the SAS Access to ODBC engine (which I assume it's not) or with the ODBC driver. My assumption is that a driver gets used in a version that's not documented as supported by SAS. 

I feel the only thing you can now do is to advice your customer to raise a track with SAS Tech Support and then take their guidance for further investigation. My advice is for the customer to communicate directly with SAS Tech Support as I can't see how you could add any value just acting as the middle man.

---

@HeatherNewton And as Tom points out SASApp-RMD-ODBC is not a libref but the name of the library metadata definition which means that potentially the tables are also defined in SAS metadata. 

It's certainly worth to check the libname definition for anything "special". I'm not aware though that there is any libname option that would change the precision of numerical variables.

Another thing to check is if there is also a metadata definition for table tb_bpe_monthly_bsa_fullbase. If so then I suggest to re-sync the metadata definition with the physical table - or eventually manually change for column PD to format best32. and no informat.

[HeatherNewton]

how can I check the metadata definition of the table?

I always thought we dont setup schema or metatable for sas tables?

[Patrick]

---

@HeatherNewton wrote:

how can I check the metadata definition of the table?

I always thought we dont setup schema or metatable for sas tables?

---

The library is clearly defined in SAS Metadata (because EG shows the metadata name, the "label"). 

You need to use SAS Management Console (SMC) to inspect the Metadata library definition. Using SMC you will then also see if there are tables as Metadata defined under this Metadata library definition (this is not a must but it's possible; it would be a must if someone would want to use such tables for building SAS DI Studio flows).

...but again: I believe things are now at a point where your customer should raise a track with SAS Tech Support.

[Ksharp]

You could code like:
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p1f29m86u65hken1deqcybowtgma.htm

proc sql;
connect to odbc(dsn= pw= user=);
select * from connection to odbc(ODBC::SQLTables) ;
quit;

[HeatherNewton]

I have now reached out to SAS technical

they are asking why is it that the pd_char has the cast function whereas the pd does not?

please kindly let me know thanks,

[Patrick]

---

@HeatherNewton wrote:

I have now reached out to SAS technical

they are asking why is it that the pd_char has the cast function whereas the pd does not?

please kindly let me know thanks,

---

??? I can't know what you've told SAS TS that makes them ask this question. We've used the cast function on the DB side to convert the numerical value to a string on the DB side to figure out in what precision the values are stored on the DB side. 

It appears you went against my advice to have the customer raise the issue with SAS TS. There are now too many middle-men in this chain. I'm not going to answer any further questions here. Let SAS TS take the lead. I suggest you share with SAS TS the link to the discussion here so they can understand what already has been discussed and looked at.

[HeatherNewton]

Hi Patrick,

Sorry. let me clarify. I didnt get customer to go to SAS tech. I took your advice to reach out to them to further resolve the whatever issue as we managed to conclude using the code you gave me to find pd and pd_char and confirm it is odbc and not my data issue. So I told them I proved by those code and request to look into it.

Sorry please dont get upset. I really appreciate your help.

OK I will work with them further.

Thanks a milion

[Patrick]

---

@HeatherNewton wrote:

Hi Patrick,

Sorry. let me clarify. I didnt get customer to go to SAS tech. I took your advice to reach out to them to further resolve the whatever issue as we managed to conclude using the code you gave me to find pd and pd_char and confirm it is odbc and not my data issue. So I told them I proved by those code and request to look into it.

Sorry please dont get upset. I really appreciate your help.

OK I will work with them further.

Thanks a milion

---

@HeatherNewton 

My advice was for your customer to directly raise this issue with SAS TS.

You don't have environment access and though piping all the communication via you as the middle-man just complicates things and will add confusion.

I still suggest you share with SAS TS the link to the discussion here as this will help them to better understand the situation and where things are at.

[Tom]

So that "SASApp-RMD-ODBC" folder in that picture that has the dataset/table in question is not a SAS libref.  Those can only be 8 characters long. Perhaps your GUI interface is showing you something that is registered in SAS metadata server?  Perhaps the metadata is registered wrong and that is what is confusing the data transfer?

[Patrick]

---

@HeatherNewton wrote:

oh I checked with user, actually they dont need to use any sas code, it is already saved in a library available

the strange thing is I have over 30 files and only this one field from 2 files has precison issue

---

If you just see the undesired result but can't investigate in detail how it gets created then there is simply no way to tell what causes the observed issue.