topic Re: Why won't this PROC SQL work in SAS Procedures

Why won't this PROC SQL work

yaswoman — Sun, 19 Jun 2011 18:30:56 GMT

Hello, I am hoping some can answer this question for me. I have the PROC sql code and running it on REMOTE sas. I was given the code from someone who uses WINSQL. But as soon as I remote submit it, it just comes back with not results and really no log. Can someone please help. I am more a SAS DATA step person so sql code is not my specialty. Thanks so much in advance.

thanks.
333 proc sql;
334 reset;
335 connect to db2(database=%sysget(DB2DBDFT));
336 create table RM_na as
337 select *
338 from connection to db2(
339 SELECT
340 cu.cust_id
341 , cu.cust_type_mn
342 , cu.cust_name_tx
343 , cu.full_busnes_na1
344 , cuacma.acct_mgr_positn_id
345 , acmpo.branch.no
346 , acmapo.team.no
347 , acmapo.positn_efectv_dt
348 , empo.emplye_li
349 , empo.first_na
350 , empo.last_na
351
352 FROM
353 EDW.cust cu
354 JOIN
355 EDW.cust_acct_mgr cuacma
356 ON
357 cu.cust_id = cuacma.cust_id
358 JOIN
359 EDW.acct_mgr_positn acmapo
360 ON
361 cuacma.acct_mgr_positn_id = acmapo.acct_mgr_positn_id
362 JOIN
363 EDW.emplye_positn empo
364 ON
365 acmapo.EMPLYE_PROFIL_ID = empo.emplye_profil_id
366
367 WHERE
368 cu.last_change_dt <='2011-05-31' AND (cu.to_dt is null or cu.to_dt > '2011-05-31' AND
369 cuacma.last_change_dt <= '2011-05-31' AND (cuacma.to_dt is null or cuacma.to_dt>
369! '2011-05-31' AND
370 acmapo.last_change_dt <= '2011-05-31' AND (acmapo.to_dt is null or acmapo.to_dt >
370! '2011-05-31' AND
371 empo.last_change_dt <= '2011-05-31' AND (empo.to_dt is null or empo.to_dt > '2011-05-31')
371! AND
372 cuacma.mgr_relatn_type_cd = 1474 AND /* Branch Account Manager AKA PRIMARY */
373 acmapo.positn_type_cd = 134 AND /* MPVB */
374 acmapo.branch_no = 4626 /*NMA */
375 );
376 disconnect from db2;
377 quit;
378 RUN;
NOTE: Remote submit to TD complete.

Re: Why won't this PROC SQL work

Doc_Duke — Sun, 19 Jun 2011 20:40:30 GMT

You probably need to work with your db2 folk, as this is a pass-thru query. I see some problems in the line 369-371 area in the log. There are no operators relating to the dates. You also have unbalanced parentheses.

Doc Muhlbaier
Duke

Re: Why won't this PROC SQL work

art297 — Sun, 19 Jun 2011 20:41:45 GMT

I'll take a guess. In your last 10 or so lines of code, three of the lines have statements that use the SAS commenting syntax (i.e., slash star, star slash).

Does the code work if you simply delete the undesired code rather than attempting to comment it out?

Also, while it shouldn't affect your run, the 'run' statement at the end isn't needed.

Art
----------
> Hello, I am hoping some can answer this question for
> me. I have the PROC sql code and running it on
> REMOTE sas. I was given the code from someone who
> uses WINSQL. But as soon as I remote submit it, it
> just comes back with not results and really no log.
> Can someone please help. I am more a SAS DATA step
> person so sql code is not my specialty. Thanks so
> much in advance.
>
> thanks.
> 333 proc sql;
> 334 reset;
> 335 connect to db2(database=%sysget(DB2DBDFT));
> 336 create table RM_na as
> 337 select *
> 338 from connection to db2(
> 339 SELECT
> 340 cu.cust_id
> 341 , cu.cust_type_mn
> 342 , cu.cust_name_tx
> 343 , cu.full_busnes_na1
> 344 , cuacma.acct_mgr_positn_id
> 345 , acmpo.branch.no
> 346 , acmapo.team.no
> 347 , acmapo.positn_efectv_dt
> 348 , empo.emplye_li
> 349 , empo.first_na
> 350 , empo.last_na
> 351
> 352 FROM
> 353 EDW.cust cu
> 354 JOIN
> 355 EDW.cust_acct_mgr cuacma
> 356 ON
> 357 cu.cust_id = cuacma.cust_id
> 358 JOIN
> 359 EDW.acct_mgr_positn acmapo
> 360 ON
> 361 cuacma.acct_mgr_positn_id =
> acmapo.acct_mgr_positn_id
> 362 JOIN
> 363 EDW.emplye_positn empo
> 364 ON
> 365 acmapo.EMPLYE_PROFIL_ID =
> empo.emplye_profil_id
> 366
> 367 WHERE
> 368 cu.last_change_dt <='2011-05-31' AND
> (cu.to_dt is null or cu.to_dt > '2011-05-31' AND
> 369 cuacma.last_change_dt <= '2011-05-31' AND
> (cuacma.to_dt is null or cuacma.to_dt>
> 369! '2011-05-31' AND
> 370 acmapo.last_change_dt <= '2011-05-31' AND
> (acmapo.to_dt is null or acmapo.to_dt >
> 370! '2011-05-31' AND
> 371 empo.last_change_dt <= '2011-05-31' AND
> (empo.to_dt is null or empo.to_dt > '2011-05-31')
> 371! AND
> 372 cuacma.mgr_relatn_type_cd = 1474 AND /*
> Branch Account Manager AKA PRIMARY */
> 373 acmapo.positn_type_cd = 134 AND /* MPVB */
> 374 acmapo.branch_no = 4626 /*NMA */
> 375 );
> 376 disconnect from db2;
> 377 quit;
> 378 RUN;
> NOTE: Remote submit to TD complete.

Re: Why won't this PROC SQL work

Patrick — Mon, 20 Jun 2011 04:02:22 GMT

Hi

You're using pass-through SQL so the SQL syntax must be in the DB2 flavor.

I believe your where clause is not valid DB2 SQL syntax. I would expect it to look something like:

WHERE
cu.last_change_dt = date('2011-05-31') AND
cuacma.last_change_dt = date('2011-05-31') AND
acmapo.last_change_dt = date('2011-05-31') AND
empo.last_change_dt = date('2011-05-31') AND
....

HTH
Patrick

Re: Why won't this PROC SQL work

yaswoman — Mon, 20 Jun 2011 13:07:59 GMT

Thank you so much for all the quick responses. Will make changes and let you know.
thanks

Re: Why won't this PROC SQL work

yaswoman — Mon, 20 Jun 2011 13:36:19 GMT

made all the changes, and works like a charm. thanks guys.