- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 12-14-2010 09:11 PM
(1124 views)
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.
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.
4 REPLIES 4
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Share some code or let us know how much detail you need to move forward.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
>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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
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