Hi, I have two simple tables, A and B
Table A looks like this:
ID | name | Comment |
---|---|---|
123 | Toyota | Good |
111 | Nissan | Good |
124 | Range Rover | Best |
Table B looks like this
ID | name | Comment | Status |
---|---|---|---|
123 | Toyota | Good | Sold |
111 | Nissan | Good | Available |
171 | Ford | Excellent | Sold |
I want a SAS SQL programme to look at table B and report status for each vehicle on table A( availability column) according to ID as follows, and return N/a when there is no ID match as follows.
ID | name | comment | Availability |
---|---|---|---|
123 | Toyota | Good | Sold |
111 | Nissan | Good | Available |
124 | Range Rover | Best | N/a |
Well, for my part:
WORK.TABLEA A
left join WORK.TABLEB B
The A after the dataset is an alias. The alias is used throughout the rest of the code, so if I select A.VAR1, then I mean VAR1 from WORK.TABLEA.
The tableb has more columns, this does not matter, in the select statement I choose the variables
A.* = all variables from tablea
case B.STATUS -> I select no variables from B, only use B.STATUS to select if I get NA or not.
As for the code presented, I would put aliases on it, and also explain "what does not work" as you have various typos and things in the code:
proc sql noprint;
create table WANT as
select A.*,
COALESCE(select THIS.STATUS from TABLEB THIS where A.ID=THIS.ID),"N/a") as AVAILABILITY
from TABLEA A;
quit;
Note in the above I am using a small trick. The coalesce function takes the first non-missing in the list of variables, so if the select subclause returns something then that is what the data will be, if there is not record then NA is used. Note also the alias used A = TABLEA, THIS = TABLEB in the subclause.
Also a good idea to format code in a readable fashion using aligned indents, etc. just for readability.
Basic SQL left join:
proc sql;
select A.*, case B.ID when . then "N/a" else B.status end as availability
from A left join B on A.ID=B.ID;
quit;
The presence of name and comment in table B is a bit puzzling. What if they don't match the values in table A for the same ID?
PG
PG Stats
Your code did not work, you did not include the create table statement, did I miss something?Please help
Hi,
The key that PGStats is making is that you need to use a Left Join in SQL on ID:
data tablea;
id=123; name="Toyota"; comment="Good"; output;
id=111; name="Nissan"; comment="Good"; output;
id=124; name="Range Rover"; comment="Best"; output;
run;
data tableb;
id=123; name="Toyota"; comment="Good"; status="Sold"; output;
id=111; name="Nissan"; comment="Good";status="Available"; output;
id=171; name="Ford"; comment="Excellent";status="Sold"; output;
run;
proc sql;
create table WANT as
select A.ID,
A.NAME,
A.COMMENT,
case when B.STATUS="" then "N/a"
else B.STATUS end as STATUS
from WORK.TABLEA A
left join WORK.TABLEB B
on A.ID=B.ID;
quit;
RW9
The problem is all my real tables have too many columns and will take time selecting all with that select statement.any easier way to include the extra column with results without using select statement?
Thanks
Well, if its laziness your after...
proc sql;
create table WANT as
select A.*,
case when B.STATUS="" then "N/a"
else B.STATUS end as STATUS
from WORK.TABLEA A
left join WORK.TABLEB B
on A.ID=B.ID;
quit;
However I would advise that you should be able to build an SQL in many ways in which variables are explicitly stated - there are reasons not to use the asterix notation. Most SQL editors have a an object browser where you can drag column names out of to save typing, you could do the same with a proc contents, or generate the code from sashelp.vcolumns etc.
PG's code missing some key variables .
proc sql;
select A.*, case B.ID when . then "N/a" else B.status end as availability
from A natural left join B ;
quit;
Xia Keshan
It all depends whether you want to join on NAME and COMMENT or not. These fields didn’t look like keys to me, when an apparently unique ID is available. - PG
RW9 sorry I'm not familiar with the join function in SQL especially this part
WORK.TABLEA A
left join WORK.TABLEB B
What does the A after " TableA" reffers to? and and also what does the B after after TABLE B? Note my real table B has more columns to the right, will it work with the code?
What about this code although still doesn't work? any suggestions?
proc sql non print;
create table want as
select tablea.* , case when exists ( select status from tableb where tablea.id=tableb.id) then cats (status)
else 'N/a' end as availability from tablea;
quit;
Well, for my part:
WORK.TABLEA A
left join WORK.TABLEB B
The A after the dataset is an alias. The alias is used throughout the rest of the code, so if I select A.VAR1, then I mean VAR1 from WORK.TABLEA.
The tableb has more columns, this does not matter, in the select statement I choose the variables
A.* = all variables from tablea
case B.STATUS -> I select no variables from B, only use B.STATUS to select if I get NA or not.
As for the code presented, I would put aliases on it, and also explain "what does not work" as you have various typos and things in the code:
proc sql noprint;
create table WANT as
select A.*,
COALESCE(select THIS.STATUS from TABLEB THIS where A.ID=THIS.ID),"N/a") as AVAILABILITY
from TABLEA A;
quit;
Note in the above I am using a small trick. The coalesce function takes the first non-missing in the list of variables, so if the select subclause returns something then that is what the data will be, if there is not record then NA is used. Note also the alias used A = TABLEA, THIS = TABLEB in the subclause.
Also a good idea to format code in a readable fashion using aligned indents, etc. just for readability.
You never requested the creation of a dataset, you said you wanted to "report status for each vehicle". That is what a SELECT statement (without a CREATE) does. If you want to create a table, add CREATE TABLE MYREPORT AS in front of the select clause.
PG
Hello,
Using Coalesce function:
proc sql;
select a.*, coalesce(b.status, "N/A") as availability from a left join b on a.id=b.id;
quit;
HI, RW9
Your coalesce code is showing an error are you sure the syntax is correct?
I have tried to use the left join function but this seems to insert additinal linae on my orginal tablea, don't know why.I'm trying your coalesce code but it is giving an error, to avoid confusion my first table is called car and the second is called car1.
can you help
Thanks.
Ah yes, missing a bracket (the select within brackets is a subquery):
proc sql noprint;
create table WANT as
select A.*,
COALESCE((select THIS.STATUS from TABLEB THIS where A.ID=THIS.ID),"N/a") as AVAILABILITY
from TABLEA A;
quit;
RW9,
The code does not work for reasons that are not syntax based. The problem with left join is that the programme adds extra lines on table a in case of duplicates on table b. All I want is something equivalent to excel vlookup, as table b has more lines and duplicates that i dont need.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.