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

i am using the below step to get obs with origin which are not in _assignments_ and only present in _values_ but not able to get.

 

proc sql;
create table sv1 as
select a.* from _values_ a left join _assignments_ b
on a.origin=b.origin and a.column=b.column;
quit;

 

I am not sure where i am wrong as i am using left join.

 

any help?

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

With a left join, you will always get all observations from the first table, and where a match is found in the other tables, their data will be added (left missing otherwise).

What you want might be achieved with

proc sql;
create table sv1 as
select a.* from _values_ a left join _assignments_ b
on a.origin=b.origin and a.column=b.column
where b.origin is missing;
quit;

or

data sv1;
merge
  _values (in=a)
  _assignments (in=b keep=origin column)
;
by origin column;
if a and not b;
run;

(proper sorting required)

View solution in original post

22 REPLIES 22
Kurt_Bremser
Super User

With a left join, you will always get all observations from the first table, and where a match is found in the other tables, their data will be added (left missing otherwise).

What you want might be achieved with

proc sql;
create table sv1 as
select a.* from _values_ a left join _assignments_ b
on a.origin=b.origin and a.column=b.column
where b.origin is missing;
quit;

or

data sv1;
merge
  _values (in=a)
  _assignments (in=b keep=origin column)
;
by origin column;
if a and not b;
run;

(proper sorting required)

alexdsa310
Obsidian | Level 7

Thanks Kurt.

 

just a small clarification

does 

where b_origin is missing;

in proc sql gives origin which is missing ?

i need if origin and column is present in both then i do not need that observation

Kurt_Bremser
Super User

When you do a left join on a condition, and no match is found from the right, then the variables from the "right" table will all be set to missing. If a match is found, the variable(s) used in the condition can not be missing, by definition.

 

Edit: your post called my attention to a typo I made (corrected in the meantime). The condition should be

where b.origin is missing;

 

alexdsa310
Obsidian | Level 7

Thanks once again kurt.

 

For my final clarification i will get only observations which will not have a common origin and column observations in both the datasets(which is what i need). i.e if i have same origin and column name in both of my datasets then i will ommit that record and only take records which has _values_  where origin and column are different and not present in _assignments_.

 

Sorry for this. its just for my understanding

Kurt_Bremser
Super User

If in doubt, do a test run and look at the results. If puzzled, inquire further.

(Maxim 4 from the Maxims of Maximally Efficient SAS Programmers).

ballardw
Super User

@Kurt_Bremser wrote:

If in doubt, do a test run and look at the results. If puzzled, inquire further.

(Maxim 4 from the Maxims of Maximally Efficient SAS Programmers).


@Kurt_Bremser do you have more for that list? My google-fu only returns the Mercenary list (which I strongly suspect was an inspiration to your response).

And now I need some Ovalqwik!

Kurt_Bremser
Super User

@ballardw wrote:

@Kurt_Bremser wrote:

If in doubt, do a test run and look at the results. If puzzled, inquire further.

(Maxim 4 from the Maxims of Maximally Efficient SAS Programmers).


@Kurt_Bremser do you have more for that list? My google-fu only returns the Mercenary list (which I strongly suspect was an inspiration to your response).

And now I need some Ovalqwik!


I already have more for that list, and I plan to release it here as an ongoing project, as I expect that many would have valid maxims to contribute. And you are, of course, completely right where the inspiration came from. Tagon's Toughs to the rescue!

alexdsa310
Obsidian | Level 7

Hi Kurt,

 

I tried the same but still facing problems as the variable origin has multiple observations and due the NULL condition it is removing all and i only want to remove if it is same with origin and column and originwhere.

Attached is the sample datasets.

 

Can you guide me if possible

Kurt_Bremser
Super User

These are not SAS datasets, but Excel spreadsheets. Use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert your example datasets into data steps for posting as text here.

Those data steps will preserve attributes (lengths, formats), and can be easily copied into SAS. MS Office files are often blocked by firewalls for security reasons.

Also post the code you used, and a quick representation of the expected result (no data step code needed there), as it differs from what you got with the code.

alexdsa310
Obsidian | Level 7

I am using the below code

proc sql;
create table sv1 as
select a.* from _values_ a left join _assignments_ b
on a.origin=b.origin and a.column=b.column
where b.origin is missing;
quit;

i need only observations which are in _values_ and not in _assignments_. so if i have a common observation in bothe the datasets with origin,originwhere i do not need them.

 

Currently it is not working as there are multiple observations of origin with diffirent column and originwhere with the code.

 

alexdsa310
Obsidian | Level 7

Yes, i tried adding it in and a.originwhere=b.originwhere but i get few observations which are in _assignments_ coming in my dataset 

Kurt_Bremser
Super User

Which of the 230 observations you get with:

proc sql;
create table sv1 as
select a.* from _values_ a left join _assignments_ b
on a.origin=b.origin and a.column=b.column and a.originwhere=b.originwhere
where b.origin is missing;
quit;

should not be in the result dataset (just provide observation numbers)?

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 22 replies
  • 1629 views
  • 3 likes
  • 3 in conversation