Help using Base SAS procedures

Append table with inconsistent structure

Reply
N/A
Posts: 0

Append table with inconsistent structure

I am currently working on two sets of tables:
1) 200k+ records  
2) 1k+ records

I would like to append the table2) into table1) each month but the most importantly, the table structure for both tables are not 100% the same. (with most of the variables in common)

Some of the variables contain the same nature of data but they are in different variable names.

Moreover, I reckon that the Cantonese wordings are not working while importing (either for the variable name or the cell inside) Does it normal that SAS is not supporting all the cantonsee wordings or something wrong with my importing procedure?

Please kindly help on appending the table. Many thanks for your great help.
Valued Guide
Posts: 632

Re: Append table with inconsistent structure

You will probably want to use the DATA step to do the appending, although a SQL step would work as well. Either way this will give you the opportunity to rename as needed. Be aware also of variables with differing attributes (length, labels, etc.). It is very possible that the change in language will also be a factor in the variable names.

Share some code or let us know how much detail you need to move forward.
Super User
Posts: 9,676

Re: Append table with inconsistent structure

Are you chinese guy?

>either for the variable name or the cell inside

You can import Chinese word just to initialize SAS with Locale encoding or use Chinese version of SAS.

moreover,SAS also allow you to use Chinese Word to be variable name ,just to use system options
[pre]
options validvarname=any;
[/pre]

But it is only applicable for SAS/Base and SAS/Stat.
For your append problem,I am afraid you need to change variable name in table2 ,and can use proc append + force to enhance efficient.


Ksharp
N/A
Posts: 0

Re: Append table with inconsistent structure

Thx guys
I'm now working on the append by using proc SQL as follow, anything wrong with the code? Sorry I'm like an ignorant and many thx for yr great help to a very beginner like myself, many thx:

proc sql;
Insert into SASUSER.IMPW0046 (Batch, Call_List_Type, BOCID, District_Code, Branch, Transaction_Time, Staff,
Customer_Type, English_Name, Chinese_Name, Gender, Age, Home_Tel_No, Mobile_Tel_No, PBS_Class, System_Code,
TRAN_DESC, Call_Date_Time_ADD, Contact_Status, Result, Language, Attempt, [Need Reply], Agent_ID, Status, Q1, Q2,
Q3, Q4, Q5, Q6, Q15, Q16, Q12, Q7, Q8, Q9, Q10, Q13, Q14, Q11, Q20, Q21, Q22, Q28, Q29, Q23, Q24, Q25, Q26,
Q27, Q50, Q51, Q50_other, Q1a_other, CIN, [Customer Key], 2010 as [year], 10 as [month])

select Batch, [Call_List_Type/ Machine_Type] as Call_List_Type, "" as BOCID, District_Code, Branch, Transaction_Time, Staff,
Customer_Type, English_Name, Chinese_Name, Gender, Age, Home_Tel_No, Mobile_Tel_No, PBS_Class, System_Code,
TRAN_DESC, Call_Date_Time_ADD, Contact_Status, "" as Result, "" as Language, Attempt, "" as [Need Reply], Agent_ID,"" as Status, Q1, Q2,
Q3, Q4, Q5, Q6, Q15, Q16, Q12, Q7, Q8, Q9, Q10, Q13, Q14, Q11, Q20, Q21, Q22, Q28, Q29, Q23, Q24, Q25, Q26,
Q27, Q50, Q51, Q50_other, Q1a_other, CIN, [Customer Key], 2010 as [year], 10 as [month]
from SASUSER.IMPW3134;
N/A
Posts: 0

Re: Append table with inconsistent structure

proc sql;
Insert into SASUSER.IMPW_0046
(xxx);
proc sql;
SELECT xx FROM SASUSER.QUERY3134;
run;



I found there is some issues for those variable name with space or slash
(e.g. last name , lastname/ name)..how can this be written in "select" or "insert" statement

and how can i rename the variable name (e.g. call_name -> call_last_name) in one table, so that it is the same as another one and do the append accordingly (e.g. call_last_name)


shall i list out all the variables in both select and insert statement? while list out, those variables not listed in the data= but in base table....can i write it as :
null as name

many thanks
Ask a Question
Discussion stats
  • 4 replies
  • 103 views
  • 0 likes
  • 3 in conversation