- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
what I'm, trying to do in this file is to eliminate only the rows in which company_no= 11802 and type=2 (colored in pink).
its look like a simple "where statement" should work but I havn't succeed to formulate the right statement yet . Any idea?
Thanks, Lior
company_no. | type | quarter |
11802 | 1 | 30/06/2012 |
11802 | 2 | 30/06/2012 |
11802 | 1 | 30/09/2012 |
11802 | 2 | 30/09/2012 |
11802 | 1 | 31/12/2012 |
11802 | 2 | 31/12/2012 |
11802 | 1 | 31/03/2013 |
11802 | 2 | 31/03/2013 |
12137 | 1 | 30/06/2012 |
12137 | 1 | 30/09/2012 |
12137 | 1 | 31/12/2012 |
12137 | 1 | 31/03/2013 |
11326 | 2 | 30/06/2012 |
11326 | 2 | 30/09/2012 |
11326 | 2 | 31/12/2012 |
11326 | 2 | 31/03/2013 |
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Proc SQL is the way to go. Step by step, First, I would think how to extract these obs, then just do the opposite by using NOT.
proc sql;
create table want as
select * from have where NOT (company_no=11802 and type=2);
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
proc sql;
create table want(drop=flag) as
select *,case when company_no=11802 and type=2 then 1 else 0 end as flag from have
where calculated flag=0;
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
thanks for the answer. the statement makes sense but when I run this statement I'm getting file "want" which is identical to the file "have". meaning I havn't succeed to eliminate the rows where
company_no=11802 and type=2.
I don't know what I'm doing wrong...
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Proc SQL is the way to go. Step by step, First, I would think how to extract these obs, then just do the opposite by using NOT.
proc sql;
create table want as
select * from have where NOT (company_no=11802 and type=2);
quit;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
thanks for the answer. the statement makes sense but when I run this statement I'm getting file "want" which is identical to the file "have". meaning I havn't succeed to eliminate the rows where
company_no=11802 and type=2.
I don't know what I'm doing wrong...
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
One possibility is that the variable type. If your company_no AND/OR type is char, then you need to put quotes around the value:
company_no='11802' and type='2'
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
its indeed char, and I already put quotes around the value.
but the results are the same .
by the way when revewing the log, everything is O.K (there is no error messege in the log) I just keep getting an adentical file.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
if both variables are chars, make sure there aren't invisible 'things' (such as blank, tab etc) in front of the visible content, try :
left(company_no)='11802' and left(type)='2'
or compress(company_no)='11802' and compress(type)='2'
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Apparently I havn't wrote the values '11802 and '2' in the right way (its should to be '0011802' and '02)'...
after I correct it the statement work fine.
Thanks you very much,
Lior