BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
librasonali
Quartz | Level 8

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; 
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

9 REPLIES 9
librasonali
Quartz | Level 8
hi thank you for the response can u help me with correct query pls?
librasonali
Quartz | Level 8
 
Kurt_Bremser
Super User

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
Quartz | Level 8
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
Super User

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
Quartz | Level 8

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. 

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
Super User

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
Super User

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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1021 views
  • 2 likes
  • 3 in conversation