BookmarkSubscribeRSS Feed
Peter_C
Rhodochrosite | Level 12

this problem arises in my UE which currently is up-to-date, here running SAS Studio 3.5

Testing the Visual Programmer QUERY builder, I found a problem with the table join:

- the form collecting details of the join does not open "Join Conditions" as expected in the documentation at http://go.documentation.sas.com/?cdcId=webeditorcdc&cdcVersion=3.6&docsetId=webeditorug&docsetTarget...

 

- the generated code has lost the "from" info 

/* Query code generated for SAS Studio by Common Query Services */

PROC SQL; 
CREATE TABLE WORK.QUERY 
AS 
SELECT CARS.MSRP 
AS x, CARS.MSRP 
FROM unknown table string null unknown table string 
ON ; 
QUIT;

Is this just my problem with UE?

Is it fixed in 3.6?

Will 3.6 be rolled out to UE some time soon?

 

17 REPLIES 17
DarthPathos
Lapis Lazuli | Level 10

Hi Peter,

 

I'm trying this on UE (Google Chrome, Mac Book, updated version of everything except Virtual Box).  I'm getting similar behaviour but different symptoms.  Dragging and dropping the second instance of CLASSFIT doesn't join as the documentation said it should, but my query runs (albeit with a full join).  Odd, as I remember testing this when the functionality first came out and it worked.  I'm going to tag @AmyP_sas in the hopes she has some ideas.  

 

I'll try on SAS Studio from my work computer today if I can and post back anything different.

Thanks

Chris

 

Screen Shot 2017-02-18 at 8.40.20 AM.png

Has my article or post helped? Please mark as Solution or Like the article!
AmyP_sas
SAS Employee

This is all very weird.  No, I've not seen either behaviour in the query tool and I'm not able to reproduce though I'm not sure of your exact steps.  If you both can give me your steps (since you both are getting different odd results) either here, via private message, or by opening a track, then we can dig deeper.  

 

Just FYI, Studio 3.6 is available via University Edition - it was released in December -  but you have to download it, we were not able to offer it as an update.  See https://communities.sas.com/t5/SAS-Analytics-U/New-release-of-SAS-University-Edition-is-now-availabl... for the announcement & details.  But the query tool did not change between 3.5 & 3.6 so you shouldn't have to update to fix this. 

Tom
Super User Tom
Super User

Here is a sequence for getting the strange highlighting of text in the program editor.

I am using UE (SAS/Studio 3.6) on MacBook Air running under Virtual Box and using Chrome as the browser.

 

In the program editor start typing a command that causes the editor to show possible completions for you command. Then use the arrow keys and return key to select the completion that you want.  Now try moving the mouse and it is highlighting text.

 

DarthPathos
Lapis Lazuli | Level 10

I'm getting the same behaviour and didn't realise that's what was causing it (also Macbook, Chrome, virtual box, current version of SAS UE).

 

@AmyP_sas - Steps I followed for the odd join behaviour were the same as those provided in the link in the OP; I wanted to see if i got the same thing using the "documented" steps (in case I had done something differently).  Using the most recent version of UE.

 

As always let me know if you need anything else from me.

Chris

Has my article or post helped? Please mark as Solution or Like the article!
Peter_C
Rhodochrosite | Level 12

sorry it has taken me time to respond :

The steps I take:

In a new Studio (UE) Visual Programmer session

1 add query object to empty process flow

2 double click the query node

3 drag sashelp.class to "Add a table to the query to begin" box

4 drag sashelp.classfit to the query box

5 in the query box, right-click and selcct "Add", then "Join"

6 In the "New Join" window, select class.class as the left table

7 in the "New Join" box, select classfit.classfit as the right table

8 leave the "join type" as inner join

9 click "save" button

 

the window described in the documentation for "Join conditions" does not appear

 

10 click on columns tab of query

11 drag table class.class to box containing "Add columns to include in the query (required)"

12 expand classfit.classfit and drag column predict to the box of selected columns

After 11 and 12, the code window populates

%web_drop_table(WORK.QUERY);
/* Query code generated for SAS Studio by Common Query Services */
PROC SQL; 
CREATE TABLE WORK.QUERY 
AS 
SELECT CLASS.Name, CLASS.Sex, CLASS.Age, CLASS.Height, CLASS.Weight, CLASSFIT.predict 
FROM unknown table string null unknown table string 
ON ; 
QUIT;
%web_open_table(WORK.QUERY) ;

Notice the FROM clause and and empty ON clause.

 

 

 

Anjali_M
SAS Employee

The steps I take to create a Join between tables:

  • In SAS Studio Visual Programmer Perspective
  • Add a Query to the Process Flow
  • Double click the Query Node to open it
  • Click the Add '+' Dropdown in the Tables tab of the Query, and select 'Table'
  • From Choose Table dialog, expand SASHELP and select CLASS table
  • Click the Add '+' Dropdown in the Tables tab and select SASHELP.CLASSFIT
  • Click the Add '+' Dropdown in the Tables tab, and select 'Join'
  • In the "New Join" window, select CLASS.CLASS as the left table, and CLASSFIT.CLASSFIT as the right table
  • Leave the 'Join type' as Inner Join

    JoinTables.PNG
  • Click the 'Save' button
  • Click on columns tab of query

  • Drag table CLASS.CLASS to  "Add columns to include in the query (required)"

  • Expand CLASSFIT.CLASSFIT and drag column 'predict' to the box of selected columns

  • QueryCode.PNG
Peter_C
Rhodochrosite | Level 12

to @DarthPathos

who asked 



Has my article or post helped? Please mark as Solution or Like the article!


sorry, but no

 The only positive step I have still to take... install the Studio3.6

 

hope the above sequence and reasult is not anything other than my "wrong turn" somewhere.

 

peterC

on windows 10 with google chrome

Version 56.0.2924.87  
Google Chrome is up to date.
as univ.ed

 

 

DarthPathos
Lapis Lazuli | Level 10

Apologies i couldn't help - but I did find my own issue, based on your original post; I'm using 3.6 so I'm hoping that your problem is resolved.  Hoping that together we are able to contribute to a more stable and solid product!

 

Have a great day

Chris

Has my article or post helped? Please mark as Solution or Like the article!
Peter_C
Rhodochrosite | Level 12
What distinguishes this (aparently working approach) from mine appears to be the way original tables are selected for te query
In my problem I used drag to select
@Anjali_M has used the "+" in the Tables tab

I shall give that a go....
Disappointing result:
if I have to avoid drag-n-drop
... especially if it works in other places🤓
Peter_C
Rhodochrosite | Level 12
An update for Studio 3.6 is available....
So I'll try before and after...

Peter_C
Rhodochrosite | Level 12
even before the latest update to Studio3.6, I can replicate both behaviours :

@Anjali_M use of the "+" icon to insert Query and tables and Join...... which generates valid code

@Peter_C dragging (almost any part of the query ) from the left pane, or using right-click in the query image to Add the Join..... then fails to display the join conditions and the area to show the tables is also empty.... and resulting code does not have the "FROM" info
Anjali_M
SAS Employee

The Build date that I have:  Jan 12, 2017 5:00:00 PM

Site name: UNIVERSITY EDITION 2.4.9.4M4

 

What version of SAS do you have? 9.4M3 or 9.4M4 ?

Peter_C
Rhodochrosite | Level 12
same version, as indicated by &SYSVLONG4 9.04.01M4P11092016
Peter_C
Rhodochrosite | Level 12

with a little more trial and error:

My issue seems to result from using the "right-click-menu" to Add the Join

If instead, I use that "+" icon in the query to Add the Join, then it builds the join condition correctly

 

I could post pictures if more clarity is needed

 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 17 replies
  • 2347 views
  • 1 like
  • 5 in conversation