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

[Ronein]

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???

[Tom]

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?

[Patrick · Accepted solution]

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?

[Ronein]

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?

[Patrick]

---

@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?"

[Ronein]

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         

[Kurt_Bremser]

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).

[Ronein]

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;

[yabwon]

What is happening when you run simple:

data test;
  set enginal.ApplicationDecisions;
run;

Is the variable character or numeric then?

Bart

[yabwon]

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

Bart

[Patrick]

---

@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.

[ballardw]

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

[Ronein]

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         

[ballardw]

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         

---

[Ronein]

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;