topic Re: $ATE9 format , column change from numeric to char without asking to do it in SAS Programming

$ATE9 format , column change from numeric to char without asking to do it

Ronein — Mon, 29 Jan 2024 05:20:47 GMT

Hello

In data set enginal.ApplicationDecisions there is a column called applicationDate.

I run proc contents to see columns content information.

proc contents data=enginal.ApplicationDecisions ;Run;

and I see that column applicationDate is numeric (SAS date)

Then I run another query (Merge)

proc sql;
create table want as
select a.appIdentity,a.applicationDate 
from enginal.ApplicationDecisions as a
left join enginal.ACCOUNT as  b 
on A.appIdentity=B.appIdentity
where b.applicationAccountIndicator = 1 and a.applicationDate between '01JAN2024'd and '10JAN2024'd
;
quit;

and in the result data set (Want) column applicationDate is defined as character with strange format $ATE9.

I run proc contents

proc contents data=want;Run;

My question-

How did it happen that column attribute was changed from numeric(SAS date) into char?

In source data set column ApplicationDate didnt have any user defined format (The format was date9) and in want data set format is $ATE9 but I haven't define it in any code. How can it happen???

Re: $ATE9 format , column change from numeric to char without asking to do it

Tom — Mon, 29 Jan 2024 05:29:57 GMT

What type of libref is enginal ?

Are you reading from a SAS dataset? Or from access to some external database?

What happens if you run this code:

data test1;
  set enginal.ApplicationDecisions (obs=1);
run;
proc contents data=test1; 
run;

What happens if you merge the datasets using simple data step code instead of PROC SQL code?

Re: $ATE9 format , column change from numeric to char without asking to do it

Patrick — Mon, 29 Jan 2024 05:37:13 GMT

From what you show us that's a really weird behavior and nothing I've seen ever happening. Does this issue persist if you run your code in a new SAS Session?

If the issue persists then I suggest you contact SAS Tech Support.

For the forum here: Provide as much SAS log with logging options like msglevel=i turned on.

Not that I believe it should make a difference but asking anyway: Is your source table a SAS file or a DB table? Is your source table registered in SAS metadata?

Re: $ATE9 format , column change from numeric to char without asking to do it

Ronein — Mon, 29 Jan 2024 06:07:33 GMT

Regarding your question -"Is your source table a SAS file or a DB table? Is your source table registered in SAS metadata?"

I think but I am not sure that the source data is not sas and enginal is the libname to connect it.

Is there way to check If the source data is SAS file or data base file?

Is there a way to check If source table registered in SAS metadata?

Re: $ATE9 format , column change from numeric to char without asking to do it

Patrick — Mon, 29 Jan 2024 07:06:20 GMT

@Ronein wrote:

Regarding your question -"Is your source table a SAS file or a DB table? Is your source table registered in SAS metadata?"

I think but I am not sure that the source data is not sas and enginal is the libname to connect it.

Is there way to check If the source data is SAS file or data base file?

Is there a way to check If source table registered in SAS metadata?

If the library is not pre-assigned then the libname statement in your code will tell you if it's pointing to a folder with SAS files or to a database.

If the library is pre-assigned then...

libname enginal list;

...will tell you what engine the libname uses and the engine tells you if it's for SAS files or a database.

Use SMC for a library that's defined in SAS metadata to check if it also got table registered - and if SAS will be using table metadata to access the physical data.

What you haven't answered is my most important question: "Does this issue persist if you run your code in a new SAS Session?"

Re: $ATE9 format , column change from numeric to char without asking to do it

Ronein — Mon, 29 Jan 2024 07:25:02 GMT

I run the code

libname enginal list;

Here is the Log- I think that I see that table is in sql server??

How did it happen that column change attribute from numeric to char?

1                                                          The SAS System                             07:39 Monday, January 29, 2024

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         libname enginal list;
NOTE: Libref=   ENGINAL 
      Scope=    Object Server
      Engine=   SQLSVR
      Physical Name= Enginal
      Schema/Owner= dbo
27         
28         GOPTIONS NOACCESSIBLE;
29         %LET _CLIENTTASKLABEL=;
30         %LET _CLIENTPROCESSFLOWNAME=;
31         %LET _CLIENTPROJECTPATH=;
32         %LET _CLIENTPROJECTPATHHOST=;
33         %LET _CLIENTPROJECTNAME=;
34         %LET _SASPROGRAMFILE=;
35         %LET _SASPROGRAMFILEHOST=;
36         
37         ;*';*";*/;quit;run;
38         ODS _ALL_ CLOSE;
39         
40         
41         QUIT; RUN;
42

Re: $ATE9 format , column change from numeric to char without asking to do it

Kurt_Bremser — Mon, 29 Jan 2024 09:45:04 GMT

Bring this to the attention of SAS technical support. Something weird happens when the result is pulled into SAS, because the whole join operation is easily passed to the database (MS SQL Server).

Re: $ATE9 format , column change from numeric to char without asking to do it

Ronein — Mon, 29 Jan 2024 10:00:47 GMT

How should I connect SAS technical support?

It happens also without merge when I run distinct


proc sql;
create table example2 as
select distinct applicationDate
from  enginal.ApplicationDecisions
;
quit;

Re: $ATE9 format , column change from numeric to char without asking to do it

yabwon — Mon, 29 Jan 2024 10:44:43 GMT

What is happening when you run simple:

data test;
  set enginal.ApplicationDecisions;
run;

Is the variable character or numeric then?

Bart

Re: $ATE9 format , column change from numeric to char without asking to do it

Patrick — Mon, 29 Jan 2024 10:47:55 GMT

@Ronein wrote:

How should I connect SAS technical support?

It happens also without merge when I run distinct

Run below code (not tested but should work), contact SAS Tech Support where you describe the problem and also share the code, the full SAS log and the result from the two Proc Contents with SAS Tech Support.

proc setinit;
run;

options sastrace=',,d,' sastraceloc=saslog nostsuffix msglevel=i;

proc sql;
  connect using enginal;
  create table test1 as
  select * from connection to enginal
    (select distinct applicationDate from dbo.ApplicationDecisions)
  ;
quit;
proc sql;
  create table test2 as
  select distinct applicationDate
  from  enginal.ApplicationDecisions
  ;
quit;

proc contents data=work.test1;
run;quit;
proc contents data=work.test2;
run;quit;

I've added on top of the code Proc Setinit as this will provide SAS Tech Support with all the additional information they require like SAS version, OS, site number etc.

Re: $ATE9 format , column change from numeric to char without asking to do it

yabwon — Mon, 29 Jan 2024 10:49:29 GMT

Follow up question, what happens when you run:

data test;
  set enginal.ApplicationDecisions(dbsastype=(applicationDate='DATE'));
run;

proc sql;
create table testSQL as
select applicationDate
from enginal.ApplicationDecisions(dbsastype=(applicationDate='DATE'))
;
run;

Bart

Re: $ATE9 format , column change from numeric to char without asking to do it

ballardw — Mon, 29 Jan 2024 11:31:39 GMT

Show the LOG of everything from your proc contents for the first data set, the sql and the last proc contents.

Re: $ATE9 format , column change from numeric to char without asking to do it

Ronein — Mon, 29 Jan 2024 12:58:39 GMT

proc sql  outobs=5;
create table example1 as
select  applicationDate
from  enginal.ApplicationDecisions
;
quit;
proc contents data=example1;Run;


proc sql;
create table example2 as
select distinct applicationDate
from  enginal.ApplicationDecisions
;
quit;
proc contents data=example1;Run;

Here is the Log

1                                                          The SAS System                             07:39 Monday, January 29, 2024

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program (2)';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         proc sql  outobs=5;
27         create table example1 as
28         select  applicationDate
29         from  enginal.ApplicationDecisions
30         ;
NOTE: Compression was disabled for data set WORK.EXAMPLE1 because compression overhead would increase the size of the data set.
WARNING: Statement terminated early due to OUTOBS=5 option.
NOTE: Table WORK.EXAMPLE1 created, with 5 rows and 1 columns.

31         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.14 seconds
      user cpu time       0.01 seconds
      system cpu time     0.02 seconds
      memory              5359.71k
      OS Memory           33708.00k
      Timestamp           01/29/2024 02:57:50 PM
      Step Count                        356  Switch Count  2
      Page Faults                       0
      Page Reclaims                     136
      Page Swaps                        0
      Voluntary Context Switches        147
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

32         proc contents data=example1;Run;

NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.03 seconds
      user cpu time       0.01 seconds
2                                                          The SAS System                             07:39 Monday, January 29, 2024

      system cpu time     0.00 seconds
      memory              996.96k
      OS Memory           28584.00k
      Timestamp           01/29/2024 02:57:50 PM
      Step Count                        357  Switch Count  0
      Page Faults                       0
      Page Reclaims                     61
      Page Swaps                        0
      Voluntary Context Switches        35
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

33         
34         
35         proc sql;
36         create table example2 as
37         select distinct applicationDate
38         from  enginal.ApplicationDecisions
39         ;
NOTE: Compression was disabled for data set WORK.EXAMPLE2 because compression overhead would increase the size of the data set.
NOTE: Table WORK.EXAMPLE2 created, with 1671 rows and 1 columns.

40         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           2.00 seconds
      user cpu time       0.02 seconds
      system cpu time     0.03 seconds
      memory              5359.43k
      OS Memory           33964.00k
      Timestamp           01/29/2024 02:57:52 PM
      Step Count                        358  Switch Count  2
      Page Faults                       0
      Page Reclaims                     229
      Page Swaps                        0
      Voluntary Context Switches        268
      Involuntary Context Switches      2
      Block Input Operations            0
      Block Output Operations           0
      

41         proc contents data=example1;Run;

NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.01 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              559.75k
      OS Memory           29096.00k
      Timestamp           01/29/2024 02:57:52 PM
      Step Count                        359  Switch Count  0
      Page Faults                       0
      Page Reclaims                     22
      Page Swaps                        0
      Voluntary Context Switches        7
      Involuntary Context Switches      0
      Block Input Operations            0
3                                                          The SAS System                             07:39 Monday, January 29, 2024

      Block Output Operations           0
      

42         
43         
44         
45         GOPTIONS NOACCESSIBLE;
46         %LET _CLIENTTASKLABEL=;
47         %LET _CLIENTPROCESSFLOWNAME=;
48         %LET _CLIENTPROJECTPATH=;
49         %LET _CLIENTPROJECTPATHHOST=;
50         %LET _CLIENTPROJECTNAME=;
51         %LET _SASPROGRAMFILE=;
52         %LET _SASPROGRAMFILEHOST=;
53         
54         ;*';*";*/;quit;run;
55         ODS _ALL_ CLOSE;
56         
57         
58         QUIT; RUN;
59

Re: $ATE9 format , column change from numeric to char without asking to do it

ballardw — Mon, 29 Jan 2024 13:15:34 GMT

If the results of two identical Proc contents calls differ you have a serious problem.

Your proc contents is NOT of the set

 enginal.ApplicationDecisions

but a different set made from that one. Since we don't know if that set is from an external data source that means Example1 could be getting some default conversion in the back ground.

And why isn't the second Proc Contents on Example 2?

The Sql code is also different than in your question where there was a join involved. So you need to show the contents of Enginal.ApplicationDecisions (not Example1) and the resulting set Example2 to discuss these results.

@Ronein wrote:

proc sql  outobs=5;
create table example1 as
select  applicationDate
from  enginal.ApplicationDecisions
;
quit;
proc contents data=example1;Run;


proc sql;
create table example2 as
select distinct applicationDate
from  enginal.ApplicationDecisions
;
quit;
proc contents data=example1;Run;

Here is the Log

1                                                          The SAS System                             07:39 Monday, January 29, 2024

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program (2)';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         proc sql  outobs=5;
27         create table example1 as
28         select  applicationDate
29         from  enginal.ApplicationDecisions
30         ;
NOTE: Compression was disabled for data set WORK.EXAMPLE1 because compression overhead would increase the size of the data set.
WARNING: Statement terminated early due to OUTOBS=5 option.
NOTE: Table WORK.EXAMPLE1 created, with 5 rows and 1 columns.

31         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.14 seconds
      user cpu time       0.01 seconds
      system cpu time     0.02 seconds
      memory              5359.71k
      OS Memory           33708.00k
      Timestamp           01/29/2024 02:57:50 PM
      Step Count                        356  Switch Count  2
      Page Faults                       0
      Page Reclaims                     136
      Page Swaps                        0
      Voluntary Context Switches        147
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

32         proc contents data=example1;Run;

NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.03 seconds
      user cpu time       0.01 seconds
2                                                          The SAS System                             07:39 Monday, January 29, 2024

      system cpu time     0.00 seconds
      memory              996.96k
      OS Memory           28584.00k
      Timestamp           01/29/2024 02:57:50 PM
      Step Count                        357  Switch Count  0
      Page Faults                       0
      Page Reclaims                     61
      Page Swaps                        0
      Voluntary Context Switches        35
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

33         
34         
35         proc sql;
36         create table example2 as
37         select distinct applicationDate
38         from  enginal.ApplicationDecisions
39         ;
NOTE: Compression was disabled for data set WORK.EXAMPLE2 because compression overhead would increase the size of the data set.
NOTE: Table WORK.EXAMPLE2 created, with 1671 rows and 1 columns.

40         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           2.00 seconds
      user cpu time       0.02 seconds
      system cpu time     0.03 seconds
      memory              5359.43k
      OS Memory           33964.00k
      Timestamp           01/29/2024 02:57:52 PM
      Step Count                        358  Switch Count  2
      Page Faults                       0
      Page Reclaims                     229
      Page Swaps                        0
      Voluntary Context Switches        268
      Involuntary Context Switches      2
      Block Input Operations            0
      Block Output Operations           0
      

41         proc contents data=example1;Run;

NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.01 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              559.75k
      OS Memory           29096.00k
      Timestamp           01/29/2024 02:57:52 PM
      Step Count                        359  Switch Count  0
      Page Faults                       0
      Page Reclaims                     22
      Page Swaps                        0
      Voluntary Context Switches        7
      Involuntary Context Switches      0
      Block Input Operations            0
3                                                          The SAS System                             07:39 Monday, January 29, 2024

      Block Output Operations           0
      

42         
43         
44         
45         GOPTIONS NOACCESSIBLE;
46         %LET _CLIENTTASKLABEL=;
47         %LET _CLIENTPROCESSFLOWNAME=;
48         %LET _CLIENTPROJECTPATH=;
49         %LET _CLIENTPROJECTPATHHOST=;
50         %LET _CLIENTPROJECTNAME=;
51         %LET _SASPROGRAMFILE=;
52         %LET _SASPROGRAMFILEHOST=;
53         
54         ;*';*";*/;quit;run;
55         ODS _ALL_ CLOSE;
56         
57         
58         QUIT; RUN;
59

Re: $ATE9 format , column change from numeric to char without asking to do it

Ronein — Mon, 29 Jan 2024 13:58:07 GMT

When I run this code then it works fine and the field stay numeric .

I just wonder why did it happend?


Data  tbl;
set enginal.ApplicationDecisions(obs=100);
format applicationDate;
format applicationDate ddmmyy10.;
Run;

proc sql;
create table example2 as
select distinct applicationDate
from  tbl
;
quit;

Re: $ATE9 format , column change from numeric to char without asking to do it

ballardw — Mon, 29 Jan 2024 16:18:21 GMT

Since your original SQL shown had a join with another table then it may have something to do with that other table. Especially if these are external DBMS sources.

Re: $ATE9 format , column change from numeric to char without asking to do it

Ronein — Tue, 30 Jan 2024 05:49:55 GMT

I show that it also happens using distinct (Without merge)

Re: $ATE9 format , column change from numeric to char without asking to do it

Patrick — Tue, 30 Jan 2024 06:53:20 GMT

@Ronein This looks like some bug. My guess is that it's driver related - may-be some unsupported version gets used. You've created and shared some clear test cases that demonstrate it's not just some coding issue. My advice remains that you should directly contact SAS Tech Support so they can guide you further. And I've already shared some code and "instructions" what I believe would help SAS Tech Support to investigate.

Re: $ATE9 format , column change from numeric to char without asking to do it

Ronein — Tue, 30 Jan 2024 20:53:12 GMT

We found the bag with SAS technical support help.

We need to change the driver in ODCB from DATADIRECT driver to SAS driver.

Can anyone explain what does it mean driver?(The SAS admin deal with it but I just curious )

Re: $ATE9 format , column change from numeric to char without asking to do it

ballardw — Tue, 30 Jan 2024 23:39:43 GMT

"Drivers" are communication instructions. How to send text to a printer for example is a "printer driver", create sound from a sound file uses a sound card driver to send instructions to the sound system. ODBC links are "drivers" to communicate data between systems.