Value not showing in the table

[Sandeep77]

Hi all,

I am trying to run a pre-written SAS code. I am just adding one column (rep_code) in the existing code and after it runs successfully, newly added column does not show any value. There is no warning or error. Can you please suggest what is the possible reason? The code is: 

proc sql;
connect to oledb (provider=sqlncli11.1
            properties = ("Integrated Security" = SSPI
            "Persist Security Info" = False
            "Initial Catalog" = pinsys
            prompt = NO
            "Data Source" = 'ELECTRA'
            read_lock_type = no));

create table Manual_Trace as
	select debt_code,
			tx_curbal as Balance,
			client_code,
			op_code,
			tran_code,
			rep_code, /*added by me */
		case when missing(dt_tx_date) then &Fin_end_dt 
			else Min(dt_tx_date) 
		end as TPT_Date format datetime22.3
			from (select distinct * from connection to oledb 
					(select a.debt_code,
							a.tran_code,
				            a.tx_date,
				            a.tx_time,
					a.dt_tx_date,
					a.tx_curbal,
					a.client_code,
					a.tran_code,
					a.rep_code /* added by me */

             			from [Pinsys].[dbo].[debt_trans] as a
        					inner join [Pinsys].[dbo].[transdescrn] as b
        						on a.Tran_code = b.Tran_code and a.tran_code in ('MO9741','MO9742','MO9743','MO9744','MO9745','MO9746','MO9747','MO9748');)) 
					where (dt_tx_date > '01JAN2022'd) and (tx_date > '01JAN2022'd) 
						group by debt_code
							order by debt_code, dt_tx_date;
quit;

[ballardw]

Can you show the log?

Are you sure that there are actually values for that variable in the source that meat your conditions?

 a.tran_code in ('MO9741','MO9742','MO9743','MO9744','MO9745','MO9746','MO9747','MO9748');)) 
					where (dt_tx_date > '01JAN2022'd) and (tx_date > '01JAN2022'd) 

[Sandeep77]

Hi,

Yes, it runs fine with all the values if I remove the rep_code and rep_code does have values in the debt_trans table. Please check the log:

29         proc sql;
30         connect to oledb (provider=sqlncli11.1
31                     properties = ("Integrated Security" = SSPI
32                     "Persist Security Info" = False
33                     "Initial Catalog" = pinsys
34                     prompt = XX
35                     "Data Source" = 'ELECTRA'
36                     read_lock_type = no));

37         
38         create table Manual_Trace as
39         	select debt_code,
40         			tx_curbal as Balance,
41         			client_code,
42         			op_code,
43         			tran_code,
44         			rep_code,
45         		case when missing(dt_tx_date) then &Fin_end_dt
46         			else Min(dt_tx_date)
47         		end as TPT_Date format datetime22.3
48         			from (select distinct * from connection to oledb
49         					(select a.debt_code,
50         							a.tran_code,
51         				            a.tx_date,
52         				            a.tx_time,
2                                                          The SAS System                             09:05 Friday, January 13, 2023

53         							a.dt_tx_date,
57         							a.tx_curbal,
58         							a.client_code,
60         							a.rep_code

63                      			from [Pinsys].[dbo].[debt_trans] as a
64                 					inner join [Pinsys].[dbo].[transdescrn] as b
65                 						on a.Tran_code = b.Tran_code and a.tran_code in
65       ! ('MO9741','MO9742','MO9743','MO9744','MO9745','MO9746','MO9747','MO9748');))
66         					where (dt_tx_date > '01JAN2022'd) and (tx_date > '01JAN2022'd) 

67         						group by debt_code
68         							order by debt_code, dt_tx_date;
NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Compressing data set WORK.MANUAL_TRACE decreased size by 40.00 percent. 
      Compressed is 417 pages; un-compressed would require 695 pages.
NOTE: Table WORK.MANUAL_TRACE created, with 436776 rows and 7 columns.

69         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           5.23 seconds
      user cpu time       2.46 seconds
      system cpu time     1.85 seconds
      memory              1195296.35k
      OS Memory           1225448.00k
      Timestamp           01/13/2023 03:01:24 PM
      Step Count                        184  Switch Count  6
      

70         
71         %LET _CLIENTTASKLABEL=;
72         %LET _CLIENTPROCESSFLOWNAME=;
73         %LET _CLIENTPROJECTPATH=;
74         %LET _CLIENTPROJECTPATHHOST=;
75         %LET _CLIENTPROJECTNAME=;
76         %LET _SASPROGRAMFILE=;
77         %LET _SASPROGRAMFILEHOST=;
78         
79         ;*';*";*/;quit;run;
80         ODS _ALL_ CLOSE;
81         
82         
83         QUIT; RUN;
84         

[LinusH]

I find it helpful to break up your query to understand where it "fails".

So first take you inner query and execute it directly in your RDBMS.

Next step make a select * from connection to and evaluate the results.

And so forth.

What is the data type for your new column?

On a general note I don't see a reason too have a part of the query as explicit pass-through. Also, don't you want to execute the distinct part in the RDBS?

[Sandeep77]

Hi, Data type of rep_code is character.