order by statement in teradata SAS

[librasonali]

Hi Team,

I am not able to get the output as i am using of the table customer_css as I am using order by statement for cust_detail_ky
which is the derived field how else I can do so that i am able to append the data ? Help will be appreciated 

proc sql;
connect to teradata (user="" password="" server="");
create table customer_int as select * from connection to teradata
(select distinct

cust_detail_ky,
cust_x,
data_source_cd,
subgrp_1_cd,
subgrp_2_cd,
from table 1
where DATA_SOURCE_CD not in (1,2,6,8,14,16,18,21,40,41,43,44,45)
order by cust_detail_ky);
quit;

proc sql;
connect to teradata (user="" password="" server="");
create table customer_css as
select * 
from connection to teradata
(select distinct
'C' as int_ext_src,
CUST as cust_x,
ORIG_SRC_SYS_CD as data_source_cd,
subgrp_1_cd,
subgrp_2_cd,

from table b)
order by cust_detail_ky;

quit;


/**************************************************************
* Now combine the data
**************************************************************/
proc append data=customer_int base=customer_css; run; 

[Kurt_Bremser]

Your explicit pass-through is not properly terminated with a closing bracket, so SAS code is sent to Teradata. This can't work.

[librasonali]

hi thank you for the response can u help me with correct query pls?

[Kurt_Bremser]

Please do not double-post. If you think a post did not work, reload the community page first before trying again. If you find you have accidentally double-posted, you yourself can delete the second post (I can't, I can only move).

[librasonali]

sorry i don't know why it accidently posted and now answer is also accepted .. mistankely .. can u pls help me with answer ?

[Kurt_Bremser]

Start by writing code instead of badly structured garbage. I am intentionally rude here, as to get your attention. As long as you do not use proper indentation (and non-indentation for statements that end a block), you will make it hard for yourself to see issues.

See this annotated code:

proc sql;
connect to teradata (user="" password="" server="UDWTEST");
create table customer_int as
  select * from connection to teradata ( /* this opens the explicit pass-through */
    select distinct
      'I' as int_ext_src,
      cust_detail_ky,
      cust_seg_nbr,
      data_source_cd,
      subgrp_1_cd,
      subgrp_2_cd,
      ben_strct_1_cd,
      ben_strct_2_cd,
      franch_1_cd,
      franch_2_cd
	from ecro.customer_detail_lu
	where DATA_SOURCE_CD  not in (1,2,6,8,14,16,18,21,40,41,43,44,45)
	order by cust_detail_ky
	/* but where is the closing bracket for the pass-through? */
; /* the ending semicolon (for the SAS SELECT!) has to be written on the same screen column where the statement starts */
quit; /* equally, the QUIT must be at the same column where PROC SQL is */

Keep in mind that the pass-through does not need a terminating semicolon, and that QUIT in Teradata ends a session. But your QUIT is intended for the SAS SQL procedure.

[librasonali]

Hi ,

I did added the brackets in my main code , while copy pasting maybe i end up missing the brackets. Apologies
I am getting this error :

NOTE: Appending WORK.CUSTOMER_INT to WORK.CUSTOMER_CSS.
WARNING: Variable data_source_cd not appended because of type mismatch.
WARNING: Variable SUBGRP_1_CD has different lengths on BASE and DATA files (BASE 5 DATA 15).
WARNING: Variable SUBGRP_2_CD has different lengths on BASE and DATA files (BASE 50 DATA 15).
WARNING: Variable BEN_STRCT_1_CD has different lengths on BASE and DATA files (BASE 4 DATA 15).
WARNING: Variable BEN_STRCT_2_CD has different lengths on BASE and DATA files (BASE 4 DATA 15).
WARNING: Variable franch_1_cd has different lengths on BASE and DATA files (BASE 30 DATA 12).
WARNING: Variable franch_2_cd has different lengths on BASE and DATA files (BASE 30 DATA 12).
WARNING: Variable cust_detail_ky not appended because of type mismatch.
ERROR: No appending done because of anomalies listed above. Use FORCE option to append these files.

therefor in my question i mentioned that issue is coming due to maybe order by cust_detail_ky??

also just to add 
from ECRO. 
1 

Column Name	Data Type	Length	Scale	Not Null	Auto Generated	Auto Increment	Default	Description
CUST_DETAIL_KY	INTEGER	10	[NULL]	TRUE	FALSE	FALSE	[NULL]	[NULL]

but in CRMINT : derving from cust seg num 

CUST_SEG_NUM

3

VARCHAR

20

[NULL]

FALSE

FALSE

FALSE

[NULL]

[NULL]

I have to change the format 

[Kurt_Bremser · Accepted solution]

Please READ the log. It tells you exactly what the problems are, and they have NOTHING to do with order. You can only append to a dataset when both datasets have ALL variables defined with identical types and lengths. If that is not the case, the physical structure of observations is different, and you cannot append. You need to make sure that that the variables are defined identically in both datasets.

WARNING: Variable data_source_cd not appended because of type mismatch.

The variable is numeric in one dataset, and character in the other.

WARNING: Variable SUBGRP_1_CD has different lengths on BASE and DATA files (BASE 5 DATA 15).
WARNING: Variable SUBGRP_2_CD has different lengths on BASE and DATA files (BASE 50 DATA 15).
WARNING: Variable BEN_STRCT_1_CD has different lengths on BASE and DATA files (BASE 4 DATA 15).
WARNING: Variable BEN_STRCT_2_CD has different lengths on BASE and DATA files (BASE 4 DATA 15).
WARNING: Variable franch_1_cd has different lengths on BASE and DATA files (BASE 30 DATA 12).
WARNING: Variable franch_2_cd has different lengths on BASE and DATA files (BASE 30 DATA 12).

Here, the lengths differ, and you are even told the different lengths.

WARNING: Variable cust_detail_ky not appended because of type mismatch.

Once again, different types.

You need to correct all this. Set up a data dictionary where you record all types/lengths/formats for variables used in your database, and structure all your SAS data in the same way, so you have uniform structures throughout.

[ballardw]

These warnings and the ERROR tell me that your data isn't ready to be appended.

WARNING: Variable data_source_cd not appended because of type mismatch.

Means that variable is character in one set and numeric in the other and will fail the append regardless as SAS won't do that. Your messages all relate the content of variables.

It would have nothing to do with order. Append places the first record of the Data set immediately after the last record of the Base data set in the order they appear.

Proc append tells you when you try to add values to the base data set that have different properties.

If the variables are the same name and type but the lengths different you add the FORCE option on the Proc statement to do the append anyway. The default as you see is not to add the records because of the difference.

The FORCE option in most cases means that you may lose data. If you try to append a variable that has 15 characters in the append to an existing Base data set where the variable is defined as 5 characters everything over 5 characters will be discarded.