BookmarkSubscribeRSS Feed
yaswoman
Calcite | Level 5
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.
5 REPLIES 5
Doc_Duke
Rhodochrosite | Level 12
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
art297
Opal | Level 21
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.
Patrick
Opal | Level 21
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
yaswoman
Calcite | Level 5
Thank you so much for all the quick responses. Will make changes and let you know.
thanks
yaswoman
Calcite | Level 5
made all the changes, and works like a charm. thanks guys.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1200 views
  • 0 likes
  • 4 in conversation