options validvarname=ANY; |
|
|
|
| ||
cas casdemo sessopts=(caslib=casuser timeout=1800 locale="en_US"); | ||||||
|
|
|
|
|
|
|
caslib _all_ assign; |
|
|
|
|
| |
libname pblic cas caslib=PUBLIC_DNFS ; |
|
|
| |||
libname sys cas caslib=SystemData; |
|
|
| |||
/*copy a dataset named AUDIT from sys lib to pblic lib*/ |
| |||||
Proc sql; |
|
|
|
|
|
|
|
|
|
|
|
|
|
Drop table pblic.audit; |
|
|
|
| ||
Quit; |
|
|
|
|
|
|
|
|
|
|
|
|
|
proc copy in=sys out=pblic; |
|
|
|
| ||
select AUDIT; |
|
|
|
|
| |
contents data=pblic.AUDIT ; |
|
|
|
| ||
run; |
|
|
|
|
|
|
quit; |
|
|
|
|
|
|
|
|
|
|
|
| |
PROC CASUTIL; |
|
|
|
|
| |
Promote casdata ="AUDIT" incaslib="PUBLIC_DNFS" |
|
| ||||
outcaslib ="PUBLIC_DNFS" casout ="AUDIT"; |
|
| ||||
|
|
|
|
|
|
|
RUN; |
3.After that i created append code to load AUDIT_APPEND in Audit table and scheduled daily.
But i am getting error.
ERROR: The variable ID has been specified with incompatible data types.
Please find detailed log attached.
Actually the table i created using COPY command has truncated the variable, how to avoid truncation of variables.
Please guide me on this.
Please if you can help me out in this issue.
As the message indicates, the main issue is that the ID variable has different types in the tables used.
what need to do to avoid truncation
What about the data type difference? Run PROC CONTENTS on both datasets to confirm what the data types of ID are in each.
How is your append dataset created (from which source, and show the code)
The CHAR versus VARCHAR difference explains your error. It looks like you have fixed that subsequently.
Make sure that the append dataset is created with the same variable attributes as the base dataset.
can we change the datatype of column in a table let say from character length(100) to character length(150)? what is syntax for that
or Character length(100) to varchar length(150).
This may solve the issue.
Please reply
-Regards,
Arundhati
The syntax is:
data TABLENAME;
length VARIABLENAME $100;
set TABLENAME;
run;
An example for creating a varchar variable in CAS is found here: https://documentation.sas.com/doc/en/vdmmlcdc/8.1/casdataam/p04v16to39mfjgn1scxil2cm6y5o.htm
To convert character variable x to varchar, do
data lib.dataset;
set lib.dataset (rename=(var=_var));
length var varchar(150);
var = _var;
drop _var;
run;
Lib has to be a CAS library for this.
Thank you very much for helping me out in this task and for the code its running good but If i use below code its converting URI again to character of length 600 which again fails append.
And if i used the datastep in the end after promote, it runs good but when session ends the table gets reverted and appended data will not be visible in promoted table.
PROC SQL; |
|
|
|
|
| |
create table AUDIT_APPEND as |
|
|
| |||
SELECT |
|
|
|
|
|
|
* |
|
|
|
|
|
|
FROM |
|
|
|
|
|
|
sys.audit SYS |
|
|
|
|
| |
where |
|
|
|
|
|
|
input(substr('Time Stamp'n,1,10),anydtdte12.)=today()-1; |
| |||||
RUN; |
|
|
|
|
|
|
data WORK.AUDIT_APPEND; |
|
|
|
|
|
|
PROC CASUTIL; |
|
|
|
|
| |
droptable casdata="AUDIT_APPEND" incaslib="PUBLIC_DNFS" quiet; | ||||||
RUN; |
|
|
|
|
|
|
|
|
|
|
|
|
|
PROC CASUTIL; |
|
|
|
|
| |
load data=WORK.AUDIT_APPEND outcaslib="PUBLIC_DNFS" |
| |||||
casout="AUDIT_APPEND" Promote; |
|
|
| |||
RUN; |
Thanks and regards,
Arundhati
length URI varchar(150);
var = _URI; /* you should use URI to the left, not var */
Please also post the complete log of your whole code by directly copy/pasting it into a window opened with this button:
In the future, use the "little running man" right next to it for posting your SAS code in the same manner.
Hi Kurt Bremser, find below code and log.It URI to character 600.
options validvarname=ANY; cas casdemo sessopts=(caslib=casuser timeout=1800 locale="en_US"); libname pblic cas caslib=PUBLIC_DNFS ; libname sys cas caslib=SystemData; PROC SQL; create table AUDIT_APPEND as SELECT * FROM sys.audit SYS where input(substr('Time Stamp'n,1,10),anydtdte12.)=today()-1; RUN; data WORK.AUDIT_APPEND; set WORK.AUDIT_APPEND (rename=(URI=_URI)); length URI varchar(150); URI = _URI; drop _URI; run; PROC CASUTIL; droptable casdata="AUDIT_APPEND" incaslib="PUBLIC_DNFS" quiet; RUN; PROC CASUTIL; load data=WORK.AUDIT_APPEND outcaslib="PUBLIC_DNFS" casout="AUDIT_APPEND" Promote; RUN; 1 %studio_hide_wrapper; 82 83 options validvarname=ANY; 84 cas casdemo sessopts=(caslib=casuser timeout=1800 locale="en_US"); NOTE: 'CASUSER(g1u6dha)' is now the active caslib. NOTE: The CAS statement request to update one or more session options for session CASDEMO completed. 85 libname pblic cas caslib=PUBLIC_DNFS ; NOTE: Libref PBLIC was successfully assigned as follows: Engine: CAS Physical Name: d63e2c2a-d003-ba49-b742-a5a73e85e5a4 86 libname sys cas caslib=SystemData; NOTE: Libref SYS was successfully assigned as follows: Engine: CAS Physical Name: d63e2c2a-d003-ba49-b742-a5a73e85e5a4 87 88 PROC SQL; 89 create table AUDIT_APPEND as 90 SELECT 91 * 92 FROM 93 sys.audit SYS 94 where 95 input(substr('Time Stamp'n,1,10),anydtdte12.)=today()-1; NOTE: Table WORK.AUDIT_APPEND created, with 7195 rows and 10 columns. 96 RUN; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. NOTE: PROCEDURE SQL used (Total process time): real time 0.77 seconds cpu time 0.06 seconds 97 data WORK.AUDIT_APPEND; 98 set WORK.AUDIT_APPEND (rename=(URI=_URI)); 99 length URI varchar(150); 100 URI = _URI; 101 drop _URI; 102 run; NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with options MSGLEVEL=I. NOTE: There were 7195 observations read from the data set WORK.AUDIT_APPEND. NOTE: The data set WORK.AUDIT_APPEND has 7195 observations and 10 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 103 PROC CASUTIL; NOTE: The UUID 'd63e2c2a-d003-ba49-b742-a5a73e85e5a4' is connected using session CASDEMO. 104 droptable casdata="AUDIT_APPEND" incaslib="PUBLIC_DNFS" quiet; NOTE: The Cloud Analytic Services server processed the request in 0.032048 seconds. 105 RUN; 106 NOTE: PROCEDURE CASUTIL used (Total process time): real time 0.05 seconds cpu time 0.00 seconds 107 PROC CASUTIL; NOTE: The UUID 'd63e2c2a-d003-ba49-b742-a5a73e85e5a4' is connected using session CASDEMO. 108 load data=WORK.AUDIT_APPEND outcaslib="PUBLIC_DNFS" 109 casout="AUDIT_APPEND" Promote; NOTE: WORK.AUDIT_APPEND was successfully added to the "PUBLIC_DNFS" caslib as "AUDIT_APPEND". 110 RUN; 111 112 113 %studio_hide_wrapper; 124 125
options validvarname=ANY; cas casdemo sessopts=(caslib=casuser timeout=1800 locale="en_US"); libname pblic cas caslib=PUBLIC_DNFS ; libname sys cas caslib=SystemData; PROC SQL; create table AUDIT_APPEND as SELECT * FROM sys.audit SYS where input(substr('Time Stamp'n,1,10),anydtdte12.)=today()-1; RUN; PROC CASUTIL; droptable casdata="AUDIT_APPEND" incaslib="PUBLIC_DNFS" quiet; RUN; PROC CASUTIL; load data=WORK.AUDIT_APPEND outcaslib="PUBLIC_DNFS" casout="AUDIT_APPEND" Promote; RUN; data pblic.AUDIT_APPEND; set pblic.AUDIT_APPEND (rename=(URI=_URI)); length URI varchar(150); URI = _URI; drop _URI; run; 1 %studio_hide_wrapper; 82 83 options validvarname=ANY; 84 cas casdemo sessopts=(caslib=casuser timeout=1800 locale="en_US"); NOTE: 'CASUSER(g1u6dha)' is now the active caslib. NOTE: The CAS statement request to update one or more session options for session CASDEMO completed. 85 libname pblic cas caslib=PUBLIC_DNFS ; NOTE: Libref PBLIC was successfully assigned as follows: Engine: CAS Physical Name: d63e2c2a-d003-ba49-b742-a5a73e85e5a4 86 libname sys cas caslib=SystemData; NOTE: Libref SYS was successfully assigned as follows: Engine: CAS Physical Name: d63e2c2a-d003-ba49-b742-a5a73e85e5a4 87 88 PROC SQL; 89 create table AUDIT_APPEND as 90 SELECT 91 * 92 FROM 93 sys.audit SYS 94 where 95 input(substr('Time Stamp'n,1,10),anydtdte12.)=today()-1; NOTE: Table WORK.AUDIT_APPEND created, with 7195 rows and 10 columns. 96 RUN; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. 97 NOTE: PROCEDURE SQL used (Total process time): real time 0.56 seconds cpu time 0.05 seconds 98 PROC CASUTIL; NOTE: The UUID 'd63e2c2a-d003-ba49-b742-a5a73e85e5a4' is connected using session CASDEMO. 99 droptable casdata="AUDIT_APPEND" incaslib="PUBLIC_DNFS" quiet; NOTE: The Cloud Analytic Services server processed the request in 0.011091 seconds. 100 RUN; 101 NOTE: PROCEDURE CASUTIL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds 102 PROC CASUTIL; NOTE: The UUID 'd63e2c2a-d003-ba49-b742-a5a73e85e5a4' is connected using session CASDEMO. 103 load data=WORK.AUDIT_APPEND outcaslib="PUBLIC_DNFS" 104 casout="AUDIT_APPEND" Promote; NOTE: WORK.AUDIT_APPEND was successfully added to the "PUBLIC_DNFS" caslib as "AUDIT_APPEND". 105 RUN; 106 NOTE: PROCEDURE CASUTIL used (Total process time): real time 0.06 seconds cpu time 0.00 seconds 107 data pblic.AUDIT_APPEND; 108 set pblic.AUDIT_APPEND (rename=(URI=_URI)); 109 length URI varchar(150); 110 URI = _URI; 111 drop _URI; 112 run; NOTE: Running DATA step in Cloud Analytic Services. NOTE: The DATA step will run in multiple threads. NOTE: There were 7195 observations read from the table AUDIT_APPEND in caslib PUBLIC_DNFS. NOTE: The table AUDIT_APPEND in caslib PUBLIC_DNFS has 7195 observations and 10 variables. NOTE: DATA statement used (Total process time): real time 0.13 seconds cpu time 0.00 seconds 113 114 115 %studio_hide_wrapper; 126 127
the above is second approch where URI change end with session.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!