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


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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

proc sql;

  create table want as

    select a.* from campaign as a

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

quit;

View solution in original post

8 REPLIES 8
Linlin
Lapis Lazuli | Level 10

proc sql;

  create table want as

    select a.* from campaign as a

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

quit;

Rish
Calcite | Level 5

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

Linlin
Lapis Lazuli | Level 10

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;

art297
Opal | Level 21

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?

Rish
Calcite | Level 5

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

art297
Opal | Level 21

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.

Rish
Calcite | Level 5

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;

art297
Opal | Level 21

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.



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!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 1625 views
  • 0 likes
  • 3 in conversation