topic Re: how to refer a table created in proc sql ? in SAS Studio

how to refer a table created in proc sql ?

Shrutibhatnagar — Sun, 17 Mar 2019 16:01:04 GMT

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 ....

Re: how to refer a table created in proc sql ?

Tom — Sun, 17 Mar 2019 16:14:24 GMT

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.

Re: how to refer a table created in proc sql ?

VDD — Sun, 17 Mar 2019 16:23:34 GMT

change this

(smb.PRODUCT_ID)

smb.PRODUCT_ID

that might help.

Re: how to refer a table created in proc sql ?

Shrutibhatnagar — Sun, 17 Mar 2019 16:46:51 GMT

Still does not execute .executing from past 20 min .

Re: how to refer a table created in proc sql ?

Shrutibhatnagar — Sun, 17 Mar 2019 16:48:45 GMT

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

Re: how to refer a table created in proc sql ?

Kurt_Bremser — Sun, 17 Mar 2019 17:33:03 GMT

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.

Re: how to refer a table created in proc sql ?

Shrutibhatnagar — Sun, 17 Mar 2019 17:50:26 GMT

the error given is

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;

Re: how to refer a table created in proc sql ?

Kurt_Bremser — Sun, 17 Mar 2019 18:05:25 GMT

The ERROR message is clear.

Re: how to refer a table created in proc sql ?

Shrutibhatnagar — Sun, 17 Mar 2019 18:14:06 GMT

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 ?

Re: how to refer a table created in proc sql ?

Kurt_Bremser — Sun, 17 Mar 2019 18:20:58 GMT

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

Re: how to refer a table created in proc sql ?

Tom — Sun, 17 Mar 2019 18:24:00 GMT

@Shrutibhatnagar wrote:

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 ?

If you created a table named TEST then you refer to it the same way, as TEST.

You can refer to tables using a one-level (member) or two-level (libref.member) name.

When you use a one level name SAS will normally prefix the libref of WORK. (Note that you can create a USER libref or use the associated options to override this behavior). So you could also refer to it as WORK.TEST.

If you created a work dataset and then start a new SAS session then that dataset is gone and you will need to re-create it. (Again there are options to override the default behavior of deleting work datasets when the SAS session ends)

Re: how to refer a table created in proc sql ?

Shrutibhatnagar — Sun, 17 Mar 2019 19:12:22 GMT

now i get new errors i referred the same

78 ,smb.Year

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;

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

Re: how to refer a table created in proc sql ?

Kurt_Bremser — Sun, 17 Mar 2019 20:12:33 GMT

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.

Re: how to refer a table created in proc sql ?

VDD — Sun, 17 Mar 2019 20:53:18 GMT

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;

Re: how to refer a table created in proc sql ?

Shrutibhatnagar — Mon, 18 Mar 2019 05:58:03 GMT

yes sure 🙂

but the error now says this :-

76 from work.test

77 left join smb.PRODUCT_ID

78 ,smb.Year

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;

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.

Re: how to refer a table created in proc sql ?

Kurt_Bremser — Mon, 18 Mar 2019 06:51:07 GMT

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.