firstname.lastname@example.org (Floyd Nevseta) wrote in message news:<email@example.com>...
> saqi2000@TOTALISE.CO.UK (Saqi) wrote in message news:<3E379A25@mail.totalise.co.uk>...
> > Hi sas gurus,
> > I have got a macro called ws_ids and I have 200 data sets and I want this
> > macro to extract given employees numbers and put into new data sets. What I
> > want to do supply 50 employees id at a time by using following command
> > %get_ids(123456789)
> > 50 times. But this overwrites the existing data. And I don't know any otherway
> > to do it. Can anyone help please.
> > Thanks in advance
> > Tahir
> Let me see if I understand your problem correctly. You have a macro,
> get_ids, that has only one parameter, a single employee id. The macro,
> I assume, queries some data set for this employee id and creates a new
> data set from the results of the query. However, you need to query the
> data set for 50 employee ids, not just one. Additionally, you are not
> just querying one dataset but 200 data sets. I think that summarizes
> First of all, forget about using the macro, get_ids. Think about this
> for a moment. You must run the same query 50 times per data set. With
> 200 data sets, the query will execute 10,000 times. That's insane,
> regardless of the size of the data sets.
> A better approach is to put the 50 employee ids in a data set. Then
> you can use SQL with an inner join or a data step with match-merging
> to get the resulting observations. Instead of having to execute the
> query 50 times, this method requires only one read of the data set. A
> simple query to capture this process is shown below.
> proc sql;
> create table out_ds as
> select *
> from emp_ids emp, in_ds ds
> where emp.emp_id = ds.emp_id;
> You can easily write a macro to encapsulate this query, so you can use
> it for each of the 200 data sets.
> Hopefully this gives you clearer view of your problem and helps you
> develop a better solution.
The problem is more complex than that. I have got a macro called
allids which searches all 200 data set and output employee id where it
matches with input id. I hope this makes clearer.
/*If id is not blank then output all the ap data set obs in ap*/
%if &id ^= %then
%if &g_ERROR %then
%put 'Problem with id';
Instead of typing the following code
multiple times. I want to type all the ids in a one go and get_ids
macro can read them one by one.
Thanks in advance