topic Column name and Datetime format error while importing data from SQL server in New SAS User

Column name and Datetime format error while importing data from SQL server

John04 — Wed, 25 Nov 2020 18:42:44 GMT

I am trying to import certain columns from MYSQL server and I want to change the format for Datetime fields to 'YYYY-MM-DD HH:MM' and also I need space between column names as the report that uses the data has similar format.

I am trying something like this but seems to ran into error:

PROC SQL;
CONNECT TO xxx AS xxxx (noprompt = "server=xxxxx ;DRIVER=SQL Server;Trusted Connection=yes;DATABASE=xxxx;");
CREATE TABLE X AS
	SELECT * FROM connection to A
	(
	SELECT t1.SAS data1,
		   t1.DateTime format=NLDATMS19. As DueDatetime
	FROM xxx.xxxx as t1
	);
DISCONNECT FROM xxxx;
QUIT;

Re: Column name and Datetime format error while importing data from SQL server

Reeza — Wed, 25 Nov 2020 19:00:06 GMT

In that nested SELECT query, that needs to be your MYSQL code, not SAS code. So format won't work in that one. I would bring it in raw first, and then reformat on the SAS side, in your select * from connection statement or in a second step.

Re: Column name and Datetime format error while importing data from SQL server

John04 — Wed, 25 Nov 2020 19:09:16 GMT

Thanks. I did try that and it seems to work well. The problem that occurs is when I try to schedule it in SAS Management Console, it shows error because of the white space in the column names.

Re: Column name and Datetime format error while importing data from SQL server

SASKiwi — Wed, 25 Nov 2020 19:22:47 GMT

Please post the SAS log of the scheduled job.

Re: Column name and Datetime format error while importing data from SQL server

Reeza — Wed, 25 Nov 2020 19:26:56 GMT

How are you referring to the variable? I'm guessing you're then running your batch process through a separate install that has slightly different options.

You can refer to it as :

"my variable"n - quotes and N are required.

Example:

proc sql;
select date as "Trading Day"n from sashelp.stocks;
quit;

Re: Column name and Datetime format error while importing data from SQL server

John04 — Wed, 25 Nov 2020 20:16:01 GMT

The problem is when I try to run it manually, it doesn't show any error. However, when I schedule it in SMC, it doesn't recognize some column names and stops which is why I dont have SAS log.

The error in my log.txt is Column var name could not be found in the table/view identified with the correlation name T1.

Re: Column name and Datetime format error while importing data from SQL server

Kurt_Bremser — Wed, 25 Nov 2020 20:21:42 GMT

Looks like your BatchServer runs with VALIDVARNME=V7, and your workspace server with VALIADVARNAME=ANY.

Re: Column name and Datetime format error while importing data from SQL server

Reeza — Wed, 25 Nov 2020 20:44:52 GMT

Agreed. And my guess is the variable name in the batch would then have underscores instead of the spaces. Or you can add options validvarname=ANY to the top of your program and see what happens.