BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zetter
Calcite | Level 5

Hi, I have two simple  tables, A and B

Table A looks like this:

IDname Comment
123ToyotaGood
111NissanGood
124Range RoverBest

Table B looks like this

IDnameCommentStatus
123ToyotaGoodSold
111NissanGoodAvailable
171FordExcellentSold

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.

IDnamecommentAvailability
123ToyotaGoodSold
111NissanGoodAvailable
124Range RoverBestN/a
1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

View solution in original post

15 REPLIES 15
PGStats
Opal | Level 21

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
zetter
Calcite | Level 5

PG Stats

Your code did not work, you did not  include the create table statement,  did I miss something?Please help

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

zetter
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ksharp
Super User

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

PGStats
Opal | Level 21

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

PG
zetter
Calcite | Level 5

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

PGStats
Opal | Level 21

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

PG
Loko
Barite | Level 11

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;

zetter
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

zetter
Calcite | Level 5

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.

  1. Thanks.

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!

What is Bayesian Analysis?

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.

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
  • 15 replies
  • 1449 views
  • 4 likes
  • 5 in conversation