About LL5

LL5 · ‎11-20-2020

Thanks Tom again for all the advice.

LL5 · ‎11-20-2020

Hi Quentin, thanks for this alternative and creative solution. I have noncontiguous ranges but this approach would be helpful to other work situation. Thanks again.

LL5 · ‎11-20-2020

Thanks Tom. The SAS programs are ran in batch under Unix, so it may not help even I update my SAS E.G session. I informed the SAS administrator about the warning message, it just seems there might be no way to fix this issue by simply updating the SAS code. But all the information I collected here are helpful for me to understand the reason.

LL5 · ‎11-20-2020

Thanks Ksharp. The issue is the excel file I have has utf-8 but my SAS session (E.G) has Latin1. As pointed out by Leonid's post earlier, I would need to change my SAS session to utf-8, so adding any encoding option in the code would not solve the problem. Unfortunately, either changing the file from xlsx to csv or change my SAS configuration file from Latin1 to utf-8 would not be doable in my work situation, I could only leave the warning message as it. Separately, since I have an external file instead of a sas dataset, I can't use inencoding/outencoding because it is not implemented in the XLSX engine, but good to know this technique. Thanks again.

LL5 · ‎11-19-2020

Understood and makes sense. Unfortunately I am not able to change the encoding to 'UTF-8' in my SAS session.

LL5 · ‎11-19-2020

My SAS session has encoding LATIN1. I tried to change the xlsx libname to proc import as below. I also added the encoding to the filename, but I am still getting the same warning message. filename a '/Inventory/group/sale_20200930.xlsx’ encoding='LATIN1' ; PROC IMPORT datafile = a out= inv_sept (keep = sale_amount sale_target sale_location sale_person) dbms=xlsx replace; sheet = "sale_sept"; RUN;

LL5 · ‎11-19-2020

Thanks again. This approach may not work in my work situation because there would be no human intervention since the the program runs automatically via a scheduled job, but appreciated for your advice.

LL5 · ‎11-19-2020

Thanks Leonid, but since I am reading an external excel file by using xlsx libname here, would CVP work in this case? It appears to me that CVP engine applies to sas dataset only. It'd be great if you can share some more insights.

LL5 · ‎11-19-2020

Thanks Ballardw, do you mind to show me how to make a copy of my source file without that column?

LL5 · ‎11-19-2020

Hi, I am getting a warning message of "Some character data was lost during transcoding in column" from the below codes. I have two questions and any helps or advice are gladly appreciated. 1. Is there any way to fix the warning message? 2. It appears Sale_description is causing this issue but I don't even have this variable in the keep = statement. Is there any way to only read 4 variables from the dataset so we don't see the warning message (which is caused by sale_description)? Thanks %let a = /Inventory/group/sale_20200930.xlsx ; libname sale xlsx "&a"; data inv_sept ; set sale.sale_sept (keep = sale_amount sale_target sale_location sale_person); run; WARNING: Some character data was lost during transcoding in column: Sale_description at obs 332 ........... WARNING: Some character data was lost during transcoding in column: Sale_description at obs 998 NOTE: The import data set has 1284 observations and 25 variables. NOTE: There were 1284 observations read from the data set SALE.SALE_SEPT NOTE: The data set WORK.INV_SEPT has 1284 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.60 seconds cpu time 0.59 seconds

LL5 · ‎10-09-2020

This works. Since Inventory is a character field, I did not even though about casting it to INT for the count function. Thanks a lot for this great tip. CAST(COUNT(INVENTORY) AS INT) AS INV_COUNT

LL5 · ‎10-09-2020

Thanks ChrisNZ. Actually in my production code, I do have cast(variable1 as int) as variable1, cast(variable2 as char(5)) as variable2, etc. The inventory variable does have datatype that SAS supports. The issue appears to have something to do with the count function because no matter what variable I put inside the count function, i am getting the same error message.

LL5 · ‎10-08-2020

Hi all, I am trying to run a sql pass thru query with ODBC connection to Teradata but it seems the count function is not supported by this engine. I tried a different approach by removing the count function outside of the pass thru query and it ran well. I want to find out 1) Is there a better/alternative way to accomplish the same goal and 2) why count function is not supported by the odbc engine? Below are the codes and error message for both approach. Approach 1 - this gives me error message PROC SQL; CONNECT TO teradata(user=&tduid password=&tdpwd server=axb connection=global mode=teradata fastload=yes fastexport=yes); CREATE TABLE inventory AS SELECT * FROM CONNECTION TO teradata ( SELECT count(inventory) as inv_count, region FROM warehouse.inventory WHERE sale_date = '2020-10-01' GROUP BY region ); ERROR: At least one of the columns in this DBMS table has a datatype that is not supported by this engine. Approach 2- this works but I wonder a better/alternative way because in reality, I have a lot more fields and tables. PROC SQL; CONNECT TO teradata(user=&tduid password=&tdpwd server=axb connection=global mode=teradata fastload=yes fastexport=yes); CREATE TABLE inventory AS SELECT count(inventory) as inv_count, region FROM CONNECTION TO teradata ( SELECT inventory, region FROM warehouse.inventory WHERE sale_date = '2020-10-01' ) GROUP BY region; NOTE: Table work.inventory created, with 131 rows and 15 columns. NOTE: Teradata connection: TPT FastExport has read 38112 row(s).

LL5 · ‎09-09-2020

Thanks Tom. I used this approach and now I am seeing "31AUG2020:00:00:00" for the datetime_var. I guess this will work when I loaded into sql server. data example; characterdate = '2020-08-31'; SasDate = input(characterdate,yymmdd10.); datetime_var = dhms(Sasdate,0,0,0); format datetime_var datetime20.; run;

LL5 · ‎09-09-2020

I have a sas dataset which needs to be loaded into MS Server Sql. The date fields I have in the SAS dataset are in mmddyy10. format (like 08/31/2020). However, those date columns are defined as datetime in the sql server table, all the date fields in the sql server table are shown as "2020-02-10 00:00:00.000". As the result, after I load the data from SAS to sql server, all date field become "1960-01-01 06:08:50.000". Is there a way to convert the date format to yyyy-mm-dd hh:mm:ss.ss in SAS before I load the data into SQL server? Or, is there a better alternative way to fix this problem (without altering the column type in the sql server table)? Any advice would be highly appreciated.

Online Status	Offline
Date Last Visited	‎06-14-2024 06:53 PM

Re: How to print out red colored error message inside a macro

Re: How to print out red colored error message inside a macro

How to print out red colored error message inside a macro

Re: How to convert number or string to date with yyyymm format and cre...

Re: How to convert number or string to date with yyyymm format and cre...

Re: How to convert number or string to date with yyyymm format and cre...

How to convert number or string to date with yyyymm format and create ...

Re: Why 2 call symput stmts are needed to create a macro variable base...

Re: Why 2 call symput stmts are needed to create a macro variable base...

Why 2 call symput stmts are needed to create a macro variable based on...

Re: How to print out red colored error message inside a macro

Re: How to print out red colored error message inside a macro

Re: How to convert number or string to date with yyyymm format and cre...

Re: How to convert number or string to date with yyyymm format and cre...

Re: How to convert number or string to date with yyyymm format and cre...

Re: WARNING: Some character data was lost during transcoding in column...

Re: WARNING: Some character data was lost during transcoding in column...

Re: WARNING: Some character data was lost during transcoding in column...

Re: WARNING: Some character data was lost during transcoding in column...

Re: WARNING: Some character data was lost during transcoding in column...

Re: WARNING: Some character data was lost during transcoding in column...

Re: WARNING: Some character data was lost during transcoding in column...

Re: WARNING: Some character data was lost during transcoding in column...

Re: WARNING: Some character data was lost during transcoding in column...

WARNING: Some character data was lost during transcoding in column:

Re: count function does not support by Teradata ODBC engine for SAS/Ac...

Re: count function does not support by Teradata ODBC engine for SAS/Ac...

count function does not support by Teradata ODBC engine for SAS/Access

Re: How to convert mmddyy10. format to yyyy-mm-dd hh:mm:ss.ss for date...

How to convert mmddyy10. format to yyyy-mm-dd hh:mm:ss.ss for date var...