topic Re: SAS Sql code for returning matches in SAS Procedures

SAS Sql code for returning matches

zetter — Tue, 16 Sep 2014 17:37:31 GMT

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

Re: SAS Sql code for returning matches

PGStats — Tue, 16 Sep 2014 17:52:23 GMT

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?

Re: SAS Sql code for returning matches

zetter — Wed, 17 Sep 2014 08:57:48 GMT

PG Stats

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

Re: SAS Sql code for returning matches

RW9 — Wed, 17 Sep 2014 09:26:18 GMT

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;

Re: SAS Sql code for returning matches

Loko — Wed, 17 Sep 2014 09:36:34 GMT

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;

Re: SAS Sql code for returning matches

zetter — Wed, 17 Sep 2014 09:38:07 GMT

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

Re: SAS Sql code for returning matches

RW9 — Wed, 17 Sep 2014 09:55:32 GMT

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.

Re: SAS Sql code for returning matches

Ksharp — Wed, 17 Sep 2014 13:16:59 GMT

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

Re: SAS Sql code for returning matches

PGStats — Wed, 17 Sep 2014 14:14:29 GMT

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.

Re: SAS Sql code for returning matches

PGStats — Wed, 17 Sep 2014 14:19:48 GMT

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

Re: SAS Sql code for returning matches

zetter — Wed, 17 Sep 2014 18:18:52 GMT

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;

Re: SAS Sql code for returning matches

RW9 — Wed, 17 Sep 2014 19:46:02 GMT

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.

Re: SAS Sql code for returning matches

zetter — Thu, 09 Oct 2014 07:31:16 GMT

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.

Re: SAS Sql code for returning matches

RW9 — Thu, 09 Oct 2014 08:40:39 GMT

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;

Re: SAS Sql code for returning matches

zetter — Thu, 09 Oct 2014 17:30:49 GMT

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.

Thanks.

Re: SAS Sql code for returning matches

RW9 — Thu, 09 Oct 2014 18:25:56 GMT

Well question 1 would then be, if you have duplicates, which of the duplicates you want to return the result of? Is it by date, sequence number etc. Once you identify what you want your single record to be then update the inner select to only select that row, maybe you just want

proc sql noprint;

create table WANT as

select A.*,

COALESCE((select THIS.STATUS from TABLEB THIS where A.ID=THIS.ID and THIS.DATE=(select max(B.DATE) from TABLEB B where B.ID=THIS.ID)),"N/a") as AVAILABILITY

from TABLEA A;

quit;#

Again it is exactly the same thing with a left join. Just clearly identify in your from clause what data should be coming into the join, eliminate duplicates at that stage, for instance if you assume that sold is more important then available do:

from (select ID,MAX(case when STATUS="sold" then 2 else 1 end) as MAX_STATUS from TABLEA group by ID)

This will then give one record per ID with sold preferred above available.