Help using Base SAS procedures

SAS Sql code for returning matches

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

SAS Sql code for returning matches

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

Accepted Solutions
Solution
‎09-17-2014 03:46 PM
Super User
Super User
Posts: 7,413

Re: SAS Sql code for returning matches

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


All Replies
Respected Advisor
Posts: 4,655

Re: SAS Sql code for returning matches

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
Contributor
Posts: 36

Re: SAS Sql code for returning matches

PG Stats

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

Super User
Super User
Posts: 7,413

Re: SAS Sql code for returning matches

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;

Contributor
Posts: 36

Re: SAS Sql code for returning matches

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

Super User
Super User
Posts: 7,413

Re: SAS Sql code for returning matches

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.

Super User
Posts: 9,687

Re: SAS Sql code for returning matches

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

Respected Advisor
Posts: 4,655

Re: SAS Sql code for returning matches

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
Contributor
Posts: 36

Re: SAS Sql code for returning matches

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;

Solution
‎09-17-2014 03:46 PM
Super User
Super User
Posts: 7,413

Re: SAS Sql code for returning matches

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.

Respected Advisor
Posts: 4,655

Re: SAS Sql code for returning matches

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
Super Contributor
Posts: 305

Re: SAS Sql code for returning matches

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;

Contributor
Posts: 36

Re: SAS Sql code for returning matches

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.

Super User
Super User
Posts: 7,413

Re: SAS Sql code for returning matches

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;

Contributor
Posts: 36

Re: SAS Sql code for returning matches

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.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 459 views
  • 4 likes
  • 5 in conversation