topic how to reference a file for filter in the code in Statistical Procedures

how to reference a file for filter in the code

Rish — Tue, 11 Sep 2012 17:09:02 GMT

Hi Guru's -

My current code uses hard coded filters to produce the results.

I would like the hard coded filters to be replaced with a sas dataset.

Is there an good way to do this?

for example: Current code:

data campaign;

set campaign;

where campaing id in ('012345',10123456');

run;

for example: Future code:

data campaign;

set campaign;

where campaing id in (file reference here);

run;

Thanks

Re: how to reference a file for filter in the code

Linlin — Tue, 11 Sep 2012 17:16:42 GMT

proc sql;

create table want as

select a.* from campaign as a

where a.id in (select id from your-reference);

quit;

Re: how to reference a file for filter in the code

Rish — Tue, 11 Sep 2012 17:29:47 GMT

Thanks Linlin.

Is there anyway we can do it using sas code and not proc sql?

The following are the reasons:

1) the table campaign has millions of records.

2) i am new to sas coding with little knowledge of sql commands.

Thanks

Re: how to reference a file for filter in the code

Linlin — Tue, 11 Sep 2012 17:41:14 GMT

You can use merge or hash.

example of using merge:

proc sort data=data1;
by id;
proc sort data=data2;
by id;

data want;
merge data1(in=a) data2(in=b);
by id;
if a and b;
run;

Re: how to reference a file for filter in the code

art297 — Tue, 11 Sep 2012 17:46:50 GMT

First a warning. If you actually ran your current code, your file would no longer contain millions of records. You would have replaced it with a file that ONLY contained those records which met the condition in the where statement.

Somehow I don't think you would want to do that.

Can you explain a little more about what you are trying to accomplish, possibly with a small example?

Re: how to reference a file for filter in the code

Rish — Tue, 11 Sep 2012 18:01:42 GMT

Hello Arthur -

I have got some 7 different tables where the data is stored. Each table has millions of records. Note that there are different fields (unique id) for joining this 7 tables.

The data i want to extract is based on a file/dataset that will have approx say 1000 records.

my question is - I want to avoid running the data and storing in the work folder. and use the work folder to filter through my 1000 records.

Example:

Data table1; set table1; run; (want to avoid this step if possible - by filtering 1000 records i need)

proc sort data=table1; by uniqid; run;

data file; set file; run;

proc sort data=file1; by uniqid; run;

Data combined;merge table1 (in=a) file (in=b); by uniqid; if a=b; run;

I have to repeat the above step for other 6 tables before i get my final population with required fields.

I hope this clarifies.

Thanks

Re: how to reference a file for filter in the code

art297 — Tue, 11 Sep 2012 18:33:48 GMT

Nope .. not enough clarification. Your example shows a couple of meaningless steps:

Data table1; set table1; run;

data file; set file; run;

What is the purpose of those sets of statements?

and, while we're asking, why are you sorting the data by uniqueid, but then selecting on another variable?

While I'm still not sure what you are asking, you might want to look into whether dataset views might do what you want. see, e.g., http://www2.sas.com/proceedings/sugi22/ADVTUTOR/PAPER36.PDF

Again, though, a realistic example with data would help let everyone know what you are really trying to accomplish.

Re: how to reference a file for filter in the code

Rish — Tue, 11 Sep 2012 19:47:18 GMT

Hi Arthur - sorry if i was not clear. Lets try the below code.

I am running below code for my output. Note I am using this code 5 different times for my final output.

Any advise how to improve the code for better execution?

Thanks

This is a pseudo code:

data com_Nl ;
set com_mis.cust(where=(CUST_ID IN ('00203046', '00232425')));
cust_num=((compress(CUST_ID,"0123456789","k"))*1);
run;

data com_nl (keep= cust_id cust_num cus_cd current_position);
set com_nl;
run;

data want_cus (keep=want_cus_cd cus_agency_cd);
set com_mis.want_cusestor;
run;

proc sort data=com_nl;
by cus_cd;
run;

proc sort data=want_cus;
by want_cus_cd;
run;

data combined;
merge com_nl(in=a) want_cus (in=b rename=(want_cus_cd=cus_cd));
if a=b;
by cus_cd;
run;

Re: how to reference a file for filter in the code

art297 — Tue, 11 Sep 2012 22:49:12 GMT

I still think you want to look into views .. they may well be what you are seeking.

Regardless, first consider your first two datasteps. i.e.,

data com_Nl ;
set com_mis.cust(where=(CUST_ID IN ('00203046', '00232425')));
cust_num=((compress(CUST_ID,"0123456789","k"))*1);
run;

data com_nl (keep= cust_id cust_num cus_cd current_position);
set com_nl;
run;

Why two? You could easily combine them as:

data com_Nl (keep= cust_id cust_num cus_cd current_position) ;
set com_mis.cust(where=(CUST_ID IN ('00203046', '00232425')));
cust_num=((compress(CUST_ID,"0123456789","k"))*1);
run;

Similarly, you can create any number of sas data files in just one datastep. Thus, instead of using a where statement, you could combine all of your calls in one datastep with if statements. Something like:

data com_nl (keep= cust_id cust_num cus_cd current_position)

com_whatever (keep= cust_id cust_num cus_cd current_position) ;

if CUST_ID IN ('00203046', '00232425') then output com_nl;

else if CUST_ID IN (whatever(replace with values)', 'whateverelse(replace with values') then output com_whatever;

run;

That way, all seven could be combined as one. And, if you make it a view, you only have to run it once.