About JBailey

JBailey · ‎10-31-2018

Hi @ashna The Table Loader Transformation will do this. Check out: http://support.sas.com/documentation/cdl/en/etlug/67323/HTML/default/viewer.htm#n12xc125b6bgi2n1y22hohbzoji7.htm Best wishes, Jeff

JBailey · ‎10-30-2018

Hi @Mara11 You may want to ask your professor about this. I bet that someone there has seen this before. Best wishes, Jeff

JBailey · ‎10-30-2018

Hi @Mara11 Was the file copied from your Windows environment to your Mac environment? If so, the file may be unreadable because the underlying OSes are different (Windows and UNIX). Check out PROC CPORT: it may help you. Best wishes, Jeff

JBailey · ‎10-30-2018

Hi @js5 It sounds like there may be an issue with some of your data. It sounds like there may be a format (or informat) that is truncating some of the columns. Your strategy about finding the observations which display the problem and comparing them to correct values is a strong one. Best of luck, Jeff

JBailey · ‎10-29-2018

Hi @js5 The situation you describe has historically been vendor (not SAS) defects in the ODBC driver and loader. You may want to call it a day and open a tech support track. That is probably the quickest way to get past this. That being said... I have tested this with SAS 9.5M5 and I am not seeing the behavior you describe (I may not have an ODBC driver that will show the issue). In order to help solve this we need to see the SAS code you are using to test this, and the interactions between SAS and the database. Pay special attention to the SASTRACE= option... Here is the SAS code I am submitting... libname mymssql odbc dsn=mssql user=someuser password=somepassword; data work.datetest; format x datetime22.3; input x datetime22.3; cards; 13AUG2018:16:35:00 run; options sastrace=',,,d' sastraceloc=saslog nostsuffix; proc append base=mymssql.datetest1 (bulkload=yes) data=work.datetest; run; proc append base=mymssql.datetest2 data=work.datetest; run; When I look at the resulting tables I see - 13AUG2018:16:35:00.000 - in both tables. In my SAS log I see that the CREATE TABLE statements (other than the table names )are the same for both tables. Pay special attention to the data types - I see DATETIME for both tables. Good Luck, Jeff

JBailey · ‎10-17-2018

Hi @akshatadeshpande SAS is releasing SAS/ACCESS Interface to Salesforce sometime next year. Keep your eye out for it. Best wishes, Jeff

JBailey · ‎10-14-2018

Hi @AndrewHowell I did a bit of Googling and found this... "The YARN Container Memory was smaller than the Spark Executor requirement. I set the YARN Container memory and maximum to be greater than Spark Executor Memory + Overhead. Check 'yarn.scheduler.maximum-allocation-mb' and/or 'yarn.nodemanager.resource.memory-mb'." It sounds like it might be what you are facing. Best wishes, Jeff

JBailey · ‎10-12-2018

Hi @AndrewHowell This may be a long shot but there should be space between the function call and the "as" keyword. Try changing "min(amount)as min_date" to "min(amount) as min_date". Here is the code: select distinct * from connection to SAS_HIVE ( SELECT year, month, min(amount) as min_date FROM H_TABLE WHERE ( ( year = 2017 ) AND ( month = 10 ) ) group by year, month ); Like I said, it is a long shot but could fix it. Best wishes, Jeff

JBailey · ‎10-10-2018

Hi @Tater_Salad This should work with SAS 7 if you don't use the CONNECT USING form of the CONNECT statement. Use the CONNECT statement from your example. Best wishes, Jeff

JBailey · ‎10-10-2018

Hi @SuryaKiran The example is just an example. It isn't intended as a performance tuning exercise. Your example is great! And will perform better in a real world scenario; which this is not. Your example also shows a different way to gather the macro information. So, now there is an example of pulling a macro value from a SAS function and another showing pulling a result set back. It's good because there is a place for both techniques. Thanks for reading and REALLY thanks for leaving such a great comment. Best wishes, Jeff

JBailey · ‎10-10-2018

There are times when you need to take data from a database query and put it into a SAS macro variable. Today was one of those times. It would be wrong not to share the example. This example uses SAS 9.4 and Oracle but it applies to most any database (DB2, Netezza, Teradata, Hadoop (Hive), Microsoft SQL Server, MySQL, PostgreSQL) and SAS/ACCESS product. libname myora oracle path=myora user=myuser password=mypassword; data myora.cars; set sashelp.cars; run; proc sql; connect using myora; select count(*) into :mycount from connection to myora (select * from cars); quit; %put "mycount=&mycount"; The count(*) value is contained in the &mycount SAS macro variable. You can see it in the SAS log. 8 %put "mycount=&mycount"; "mycount= 428" Hope this helps, Jeff

JBailey · ‎10-10-2018

Hi @india2016 Without detailed information it is hard to answer your question. If it were me, I would validate the data coming out of Oracle and see if there are any surprises. Once I am sure the Oracle data looks good I would load it into Hadoop. Pay attention to the data type conversions and missing values. If you can find the Oracle rows that display the problems it would help the community members understand what is happening. To really solve this we would need, at a minimum, the DDL for the table definitions in both Oracle and Hive and sample data (that causes the problem), and data (from Hive) showing the problem. Best wishes, Jeff

JBailey · ‎10-10-2018

Hi @Tater_Salad I don't have SAS 7 available (it turns out that 7 is the version of SAS Enterprise Guide - I had no idea) but here is a SAS 9.4 and Oracle example that shows how to do it. Netezza should be very similar. The CONNECT USING statement is new (and awesome). CONNECT USING isn't available in SAS 7 - you will want to use the CONNECT statement that you used in the code sample you provided. I have updated the example based on a comment by @SuryaKiran libname myora oracle path=myora user=myuser password=mypassword; data myora.cars; set sashelp.cars; run; /* This is a slow way to do it */ proc sql; connect using myora; select count(*) into :mycount from connection to myora (select * from cars); quit; /* This is a faster way to do it and the results will be the same */ proc sql; connect using myora; select this_count into :mycount from connection to myora (select count(*) as this_count from cars); quit; %put "mycount=&mycount"; The output of the %put statement is... 8 %put "mycount=&mycount"; "mycount= 428" To reference the actual SAS macro variable use only &mycount. Hope that helps, Jeff

JBailey · ‎10-09-2018

Hi @curaloco, I am happy my post solved your problem. The VARCHAR length issue is a problem with all SAS/ACCESS engines. Having a dedicated Snowflake engine will not magically fix it. Best wishes, Jeff

JBailey · ‎10-05-2018

Hi @skygold16 UPDATE! I have this working... I cannot believe it.. Part of the problem is that the arguments have different names in the JDBC driver... I copied my code from there. This actually worked for me (getting the proxy server info was the most difficult part): libname athena odbc complete="DRIVER={Simba Athena ODBC Driver}; AwsRegion=us-east-1; S3OutputLocation=s3://bogus-athena-results/; AuthenticationType=IAM Profile; AwsProfile=617292732428-jbsandbox; UseProxy=1; ProxyScheme=HTTP; ProxyHost=111.111.111.111; ProxyPort=80; ProxyUID=ProxyUserID; ProxyPWD=ProxyPassword;" schema=somedb; I am able to get this to work without specifying the ProxyUID= and ProxyPWD= options. My experience with this type of stuff is that it takes an embarrassing amount of trial and error;) <-- This is true! I am going to create an article or a blog post on this... Best wishes, Jeff

Online Status	Offline
Date Last Visited	3 weeks ago