topic Re: Proc COPY truncating variable names in SAS Viya

Proc COPY truncating variable names

arundhatiD — Mon, 27 Dec 2021 15:59:54 GMT

I am having one use case where I have one table Audit table in system data Library in sas viya which holds only last 7days data always.

I want to have more days data.As there is one report where business wants to see more days data than only last 7 days.

So I have to create one Audit table in public_dnfs library which holds data for more days i.e it maintains history.

For this I have taken following approch

1.i used copy command to copy all data from audit table from system data to public_dnfs and used promote statement.This is one time process.

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;

Now I want to update this audit table in public_dnfs by appending 1day data on each day. So i created one more table holding 1 day data which will be scheduled daily.

Ex.

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.

Below is the log:

source: 1 options validvarname=ANY;
source: 2 cas casdemo sessopts=(caslib=casuser timeout=1800 locale="en_US");
note: NOTE: The session CASDEMO connected successfully to Cloud Analytic Services P01APL30022.ent.rt.csaa.com using port 5570. The UUID
note: is ec1ef24a-6de0-c543-892c-7e50b9a29601. The user is g1u6dha and the active caslib is CASUSERHDFS(g1u6dha).
note: NOTE: The SAS option SESSREF was updated with the value CASDEMO.
note: NOTE: The SAS macro _SESSREF_ was updated with the value CASDEMO.
note: NOTE: The session is using 2 workers.
note: NOTE: 'CASUSER(g1u6dha)' is now the active caslib.
note: NOTE: The CAS statement request to update one or more session options for session CASDEMO completed.
source: 3
source: 4
source: 5 libname pblic cas caslib=PUBLIC_DNFS ;
note: NOTE: Libref PBLIC was successfully assigned as follows:
note: Engine: CAS
note: Physical Name: ec1ef24a-6de0-c543-892c-7e50b9a29601
source: 6
source: 7 data pblic.audit(append=yes);
source: 8
source: 9 set pblic.AUDIT_APPEND;
source: 10
source: 11 run;
note:
note: NOTE: Running DATA step in Cloud Analytic Services.
note: NOTE: The DATA step will run in multiple threads.
error: ERROR: The variable ID has been specified with incompatible data types.
normal:
error: ERROR: The action stopped due to errors.
note: NOTE: The SAS System stopped processing this step because of errors.
title: 2 The SAS System Sunday, December 26, 2021 03:40:00 PM
title:
note: NOTE: DATA statement used (Total process time):
note: real time 0.14 seconds
note: cpu time 0.01 seconds
note:
note:

Re: Proc COPY truncating variable names

ChrisNZ — Thu, 30 Dec 2021 01:06:10 GMT

As the message indicates, the main issue is that the ID variable has different types in the tables used.

Re: Proc COPY truncating variable names

arundhatiD — Thu, 30 Dec 2021 07:07:09 GMT

what need to do to avoid truncation

Re: Proc COPY truncating variable names

SASKiwi — Thu, 30 Dec 2021 08:28:30 GMT

What about the data type difference? Run PROC CONTENTS on both datasets to confirm what the data types of ID are in each.

Re: Proc COPY truncating variable names

Kurt_Bremser — Thu, 30 Dec 2021 09:44:11 GMT

Make sure that the append dataset is created with the same variable attributes as the base dataset.

Re: Proc COPY truncating variable names

arundhatiD — Thu, 30 Dec 2021 09:01:45 GMT

Re: Proc COPY truncating variable names

Kurt_Bremser — Thu, 30 Dec 2021 10:45:23 GMT

How is your append dataset created (from which source, and show the code)

Re: Proc COPY truncating variable names

arundhatiD — Mon, 03 Jan 2022 07:20:01 GMT

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

Re: Proc COPY truncating variable names

ChrisNZ — Mon, 03 Jan 2022 08:00:44 GMT

The syntax is:

data TABLENAME;
  length VARIABLENAME $100;
  set TABLENAME;
run;

Re: Proc COPY truncating variable names

Kurt_Bremser — Mon, 03 Jan 2022 08:15:38 GMT

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.

Re: Proc COPY truncating variable names

arundhatiD — Mon, 03 Jan 2022 16:18:37 GMT

Hi Kurt_Bremser,

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; set WORK.AUDIT_APPEND (rename=(URI=_URI)); length URI varchar(150); var = _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;

Thanks and regards,

Arundhati

Re: Proc COPY truncating variable names

Kurt_Bremser — Mon, 03 Jan 2022 17:13:17 GMT

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.

Re: Proc COPY truncating variable names

arundhatiD — Mon, 03 Jan 2022 17:30:36 GMT

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

Re: Proc COPY truncating variable names

arundhatiD — Mon, 03 Jan 2022 17:31:59 GMT

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.

Re: Proc COPY truncating variable names

Kurt_Bremser — Mon, 03 Jan 2022 17:36:33 GMT

What I expected. This is your clue:

NOTE: One or more variables were converted because the data type is not supported by the V9 engine.

All actions involving the VARCHAR type must take place in a CAS library, you cannot use WORK for this.

Re: Proc COPY truncating variable names

arundhatiD — Mon, 03 Jan 2022 17:44:14 GMT

Thank you Very much for clarification.

If I do it for promoted table ,changes disappear after session ends, is
there any other work around?
-Arundhati

--

This communication is confidential and
subject to and governed by Saama’s
Electronic
Communications Disclaimer.

Re: Proc COPY truncating variable names

SASKiwi — Mon, 03 Jan 2022 21:19:30 GMT

The CHAR versus VARCHAR difference explains your error. It looks like you have fixed that subsequently.