how to refer a table created in proc sql ?

[Shrutibhatnagar]

table 1 smb 

table 2  test:-

proc sql;
create table test as
select year, product_ID, max(AON) as MAX_AON from class1.smb
group by 1;
quit;  

i want to do left join of the smb and test table 

so i do this -

proc sql ;
select distinct
(smb.PRODUCT_ID)
,smb.Year
,smb.AON
,smb.CELL_CALL_CNT_M1
,smb.CELL_CALL_CNT_M2
,smb.CELL_CALL_CNT_M3
,smb.CELL_CALL_CNT_M4
,smb.CELL_CALL_CNT_M5
,smb.CELL_CALL_CNT_M6
,smb.CELL_CALL_SEC_M1
,smb.CELL_CALL_SEC_M2
,smb.CELL_CALL_SEC_M3
,smb.CELL_CALL_SEC_M4
,smb.CELL_CALL_SEC_M5
,smb.CELL_CALL_SEC_M6
,smb.CELL_CALL_avg_dur_M1
,smb.CELL_CALL_avg_dur_M2
,smb.CELL_CALL_avg_dur_M3
,smb.CELL_CALL_avg_dur_M4
,smb.CELL_CALL_avg_dur_M5
,smb.CELL_CALL_avg_dur_M6
,smb.CELL_USAGE_CHARGE_AMT_M1
,smb.CELL_USAGE_CHARGE_AMT_M2
,smb.CELL_USAGE_CHARGE_AMT_M3
,smb.CELL_USAGE_CHARGE_AMT_M4
,smb.CELL_USAGE_CHARGE_AMT_M5
,smb.CELL_USAGE_CHARGE_AMT_M6

from class1.smb

left join test
on smb.product_id=test.product_id;

quit;

and this doesn't execute 

please help ....

[Tom]

If this is a followup to your other message then you have included even less information than before.

There does not appear to be an mistakes in your code (other than extract parentheses around the first variable selected in your longer SQL code).  What error are you getting?

Make sure to use one of the two code insert buttons on the menu bar to insert your code and your log. They will pop-up a new window that you can paste in the text from your SAS log window. This way the formatting is preserved.

[Shrutibhatnagar]

not executing even . its been running from past 20 min .

[VDD]

change this

(smb.PRODUCT_ID)

to 

smb.PRODUCT_ID

that might help.

[Shrutibhatnagar]

Still does not execute .executing from past 20 min .

[Kurt_Bremser · Accepted solution]

Since you do a left join without using any columns from the "right" dataset, the join makes no sense.

Keep in mind that the distinct forces a sort over all variables, so that could cause heavy processing load.

If it does not do anything, you might have something "unclosed" from previous code that prevents execution.

Anyway, post the log.

[Shrutibhatnagar]

the error given is 

92

493 from class1.smb

494

495 left join test.product_id

496 on smb.product_id=test.product_id;

ERROR: Libref TEST is not assigned.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

497

498 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

code after correction -

from class1.smb

left join test.product_id
on smb.product_id=test.product_id;

quit;

[Kurt_Bremser]

The ERROR message is clear.

[Shrutibhatnagar]

i want to know how do i refer this table ,as we refer through libname when we give a path but this is created in proc statement i wrote . How to refer the test table in proc sql to be joined with smb table ?

[Kurt_Bremser]

You refer to dataset test.product_id in your join clause, which means dataset product_id in library test.

[Shrutibhatnagar]

now i get new errors i referred the same 

78 ,smb.Year

_

22

200

ERROR 22-322: Syntax error, expecting one of the following: a name, (, AS, ON.

ERROR 200-322: The symbol is not recognized and will be ignored.

79 ,smb.AON

80 ,smb.CELL_CALL_CNT_M1

81 ,smb.CELL_CALL_CNT_M2

82 ,smb.CELL_CALL_CNT_M3

83 ,smb.CELL_CALL_CNT_M4

84 ,smb.CELL_CALL_CNT_M5

85 ,smb.CELL_CALL_CNT_M6

86 ,smb.CELL_CALL_SEC_M1

87 ,smb.CELL_CALL_SEC_M2

88 ,smb.CELL_CALL_SEC_M3

89 ,smb.CELL_CALL_SEC_M4

90 ,smb.CELL_CALL_SEC_M5

91 ,smb.CELL_CALL_SEC_M6

92 ,smb.CELL_CALL_avg_dur_M1

93 ,smb.CELL_CALL_avg_dur_M2

94 ,smb.CELL_CALL_avg_dur_M3

95 ,smb.CELL_CALL_avg_dur_M4

96 ,smb.CELL_CALL_avg_dur_M5

97 ,smb.CELL_CALL_avg_dur_M6

98 ,smb.CELL_USAGE_CHARGE_AMT_M1

99 ,smb.CELL_USAGE_CHARGE_AMT_M2

100 ,smb.CELL_USAGE_CHARGE_AMT_M3

101 ,smb.CELL_USAGE_CHARGE_AMT_M4

102 ,smb.CELL_USAGE_CHARGE_AMT_M5

103 ,smb.CELL_USAGE_CHARGE_AMT_M6

492 ,smb.Avg_DEC_3M_USAGE_CHARGE_AMT as t2

493

494

495 on t1.product_id=t2.product_id;

__

22

76

ERROR 22-322: Syntax error, expecting one of the following: ;, ',', ANSIMISS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT,

JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

496

497 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.20 seconds

cpu time 0.19 seconds

498

499

500

501

502

503

504

505

506

507

508

509 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

521

[Kurt_Bremser]

Your problems start further up in the code.

I suggest that you spend some time studying the SAS SQL documentation, so you get at least a basic grasp of how the sql procedure works.

[VDD]

Listing all error related to the process would have help at the beginning of this request.  Since those important facts were excluded in the beginning time related to resolve the issue was delayed .

496 on smb.product_id=test.product_id;

ERROR: Libref TEST is not assigned.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

497

498 quit;

[Shrutibhatnagar]

yes sure 🙂

but the error now says this :-

76 from work.test

77 left join smb.PRODUCT_ID

78 ,smb.Year

_

22

200

ERROR 22-322: Syntax error, expecting one of the following: a name, (, AS, ON.

ERROR 200-322: The symbol is not recognized and will be ignored.

79 ,smb.AON

80 ,smb.CELL_CALL_CNT_M1

81 ,smb.CELL_CALL_CNT_M2

82 ,smb.CELL_CALL_CNT_M3

494 on class1.product_id=test.product_id;

__

22

76

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING,

INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.

ERROR 76-322: Syntax error, statement will be ignored.

[Kurt_Bremser]

Once again: you have to seem absolutely no clue about proc sql, so the complicated things you want to do here are WAY over your head and just a useless waste of time.

Start out with the simple examples from the documentation, and come back once you have that worked out. We're not here to teach you thinngs you can easily learn by yourself by working through Programming 1.

Just this:

77 left join smb.PRODUCT_ID
78 ,smb.Year

The list of variables in a select has to come BEFORE the "from". This is absolutely basic SQL, and you need to grasp that before you advance to any complicated join.