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

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.typequarter
11802130/06/2012
11802230/06/2012
11802130/09/2012
11802230/09/2012
11802131/12/2012
11802231/12/2012
11802131/03/2013
11802231/03/2013
12137130/06/2012
12137130/09/2012
12137131/12/2012
12137131/03/2013
11326230/06/2012
11326230/09/2012
11326231/12/2012
11326231/03/2013
1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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;

View solution in original post

8 REPLIES 8
stat_sas
Ammonite | Level 13

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;

lior
Calcite | Level 5

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...


Haikuo
Onyx | Level 15

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;

lior
Calcite | Level 5

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...


Haikuo
Onyx | Level 15

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'

lior
Calcite | Level 5

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.

Haikuo
Onyx | Level 15

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'

lior
Calcite | Level 5

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

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
  • 8 replies
  • 1458 views
  • 3 likes
  • 3 in conversation