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);
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".
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.
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;
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;
There's nothing obviously wrong with your code. How many records in each of the three data sets?
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.