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 |
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;
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;
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...
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;
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...
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'
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.
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'
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.