topic Re: subsetting data: DATA STEP in SAS Procedures

subsetting data: DATA STEP

deleted_user — Fri, 17 Oct 2008 17:45:23 GMT

Hi. I'm trying to get about 115 observations from a data set that has 10000. I want all the information for each of the 115 observations. I tried this the code below and also used an 'or' instead of the 'and'. But, I can't get a dataset with just the 115 observations. How can I do so? Thank you.

data schaudit;
set sch;
if schcode =

0100610 and
0107292 and
0109785 and
0113514 and
0114371 and
0115089 and
0115105 and
0116129 and
....i HAVE ALL 115 SCHCODES LISTED...
run; Message was edited by: jcis

Re: subsetting data: DATA STEP

LinusH — Fri, 17 Oct 2008 17:55:54 GMT

Well, the and does not work at all for your case, but or should, or more convenient in(0100610 ,0107292 , ...). Even more convenient, have your subset data driven by store your subset schcodes in a separate table:

proc sql;
create table schaudit as
select *
from sch
where schcode in(select schcode from schcodeTable)
;
quit;

Is schcode numeric or character? Your code imply numeric, but leading zeroes will be truncated automatically. And what didnt work when using OR? Please show relevant parts from your log. Are you certain that your 115 all exist in your data?

/Linus

Re: subsetting data: DATA STEP

Cynthia_sas — Fri, 17 Oct 2008 18:38:53 GMT

Hi:
The syntax of an IF (whether subsetting or with a 'then do") statement is very specific. You cannot string "OR" values/conditions together like this:[pre]
if somevar = 111 or 222 or 333 or 444 ....
[/pre]

Even though some languages might let you do this, SAS will not. Your condition must consist of expressions and logical operators. "or 222" is a valid operator (OR) followed by the constant 222 which is not a valid expression, if what you want to test is whether SOMEVAR=222. Here are some examples:
[pre]
IF somevar = 111 or somevar = 222 or somevar = 333 ....

IF somevar in (111,222,333,444) ....

IF something in ('value', 'value2', 'value3') ....

IF (something='value' AND another='value2')
OR
(somevar in (111,222)) ....
[/pre]

The other approach is to have a lookup table. At some sites, however, you are not allowed to use lookup tables because all the observations being selected must be auditable from within a single program. So, if you do need to use a subsetting IF or an IF with an OUTPUT statement, then your IF statement must follow the rules above.

cynthia

Re: subsetting data: DATA STEP

deleted_user — Fri, 17 Oct 2008 19:44:06 GMT

Thank you very much! It worked!

Re: subsetting data: DATA STEP

deleted_user — Fri, 24 Oct 2008 17:55:40 GMT

How do you use the lookup table? I have created a sas datable with
just the codes I want to select for. Then, I have the table I want to select the observations from based on select codes and update it in the Master datable.
Thanks.

Re: subsetting data: DATA STEP

LinusH — Fri, 24 Oct 2008 19:21:08 GMT

The easiest is to use SQL with a sub-query or an inner join:

Proc SQL;
create table schaudit as
select *
from sch
where schcode in (select schcodes from sch_lookUpTable)
;
create table schaudit as
select sch.*
from sch inner join
sch_lookUpTable
on sch.schcode eq sch_lookUpTable.schcode
;
quit;

/Linus

Re: subsetting data: DATA STEP

deleted_user — Mon, 27 Oct 2008 18:58:05 GMT

Thanks! I am new to Proc SQL and I love it. Appreciate your help.