Write and run SAS programs in your web browser

visual programmer QUERY in SAS Studio (ue)

Reply
Valued Guide
Posts: 2,152

visual programmer QUERY in SAS Studio (ue)

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?

 

Regular Contributor
Posts: 227

Re: visual programmer QUERY in SAS Studio (ue)

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!
SAS Employee
Posts: 145

Re: visual programmer QUERY in SAS Studio (ue)

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. 

Super User
Super User
Posts: 5,964

Re: visual programmer QUERY in SAS Studio (ue)

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.

 

Regular Contributor
Posts: 227

Re: visual programmer QUERY in SAS Studio (ue)

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!
Valued Guide
Posts: 2,152

Re: visual programmer QUERY in SAS Studio (ue)

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.

 

 

 

SAS Employee
Posts: 2

Re: visual programmer QUERY in SAS Studio (ue)

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
Valued Guide
Posts: 2,152

Re: visual programmer QUERY in SAS Studio (ue)

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

 

 

Regular Contributor
Posts: 227

Re: visual programmer QUERY in SAS Studio (ue)

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!
Valued Guide
Posts: 2,152

Re: visual programmer QUERY in SAS Studio (ue)

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🤓
Valued Guide
Posts: 2,152

Re: visual programmer QUERY in SAS Studio (ue)

An update for Studio 3.6 is available....
So I'll try before and after...

Valued Guide
Posts: 2,152

Re: visual programmer QUERY in SAS Studio (ue)

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
SAS Employee
Posts: 2

Re: visual programmer QUERY in SAS Studio (ue)

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 ?

Valued Guide
Posts: 2,152

Re: visual programmer QUERY in SAS Studio (ue)

same version, as indicated by &SYSVLONG4 9.04.01M4P11092016
Valued Guide
Posts: 2,152

Re: visual programmer QUERY in SAS Studio (ue)

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

 

Post a Question
Discussion Stats
  • 17 replies
  • 194 views
  • 1 like
  • 5 in conversation