topic Re: SAS Studio Create Table with Left Join Issue in SAS Visual Analytics

SAS Studio Create Table with Left Join Issue

abidi — Mon, 13 Sep 2021 10:01:20 GMT

Dear All,

I am using SAS Studio and SAS VA. I have 3 tables and left joined successfully and want to retrieve all the records with all fields. The query result comes up with the desired output but when I encapsulate query inside the create table statement, it retrieves only 542 fields out of 721 fields. Please find below the code for your reference:

%rskmgt;
proc sql;
Create table OBS_ENG_AP_COMPLT AS

Select *

from table a left join

table b

ON a.id = b.id left join table c

ON a.id = c.id

quit;

%rskmgt_load_lasr_tbls_no_perms(intblnm=OBS_ENG_AP_COMPLT,inlib=WORK);

Re: SAS Studio Create Table with Left Join Issue

PaigeMiller — Mon, 13 Sep 2021 10:20:33 GMT

This code seems like it is using the exact same SAS data set named TABLE for all three datasets that are going to be joined. Is that what you want? It's not what you said "I have 3 tables".

Re: SAS Studio Create Table with Left Join Issue

abidi — Mon, 13 Sep 2021 10:58:13 GMT

Yes, indeed, I am using for instance table a, table b, and table c. I have joined these tables and the code of joining the table is attached.

Re: SAS Studio Create Table with Left Join Issue

PaigeMiller — Mon, 13 Sep 2021 11:08:54 GMT

Your code is joining table to itself 3 times. You are not joining table a, table b and table c.

Maybe you want:

proc sql;
    Create table OBS_ENG_AP_COMPLT AS
    Select *
       from a left join b
       ON a.id = b.id 
       left join c
       ON a.id = c.id;
quit;

Re: SAS Studio Create Table with Left Join Issue

abidi — Mon, 13 Sep 2021 11:24:23 GMT

This is the complete code. Sorry for the inconvenience.

proc sql;

Select *
from sq.observations as obs Left Join
sq.engagement as eng
ON obs.ID = eng.CUST_OBJ_ID Left Join
sq.actionplan as actplan
ON obs.ID = actplan.CUST_OBJ_ID;

quit;

Re: SAS Studio Create Table with Left Join Issue

PaigeMiller — Mon, 13 Sep 2021 11:33:33 GMT

There's nothing obviously wrong with your code. How many records in each of the three data sets?

Re: SAS Studio Create Table with Left Join Issue

SASKiwi — Mon, 13 Sep 2021 20:21:14 GMT

What does your SAS log report regarding the number of rows and columns created? It will be similar to this:

NOTE: Table WORK.TEST created, with 19 rows and 5 columns.

Please note that if your input tables have common column names then only the columns from the first table will be selected.