|
f.nevseta@cyberdude.com (Floyd Nevseta) wrote in message news:<e73d2bd4.0301282202.48daf02c@posting.google.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
>
> Saqi,
>
> 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
> it.
>
> 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;
> quit;
>
> 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.
>
> Regards,
> Floyd
Floyd,
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.
%macro get_ids(id=ALL);
%let id=%upcase(&id);
/*If id is not blank then output all the ap data set obs in ap*/
%if &id ^= %then
%do;
%allids(dsallapp=work.ap,id=&id)
%if &g_ERROR %then
%do;
%put 'Problem with id';
%goto halt;
%end;
%end;
run;
data work.jobline;
set employee.jobline;
where id=&id;
run;
data work.idline;
set employee.idline;
where id=&id;
run;
%halt:
%mend get_ids;
%get_ids(id=123456789);
%get_ids(id=987654321);
Instead of typing the following code
%get_ids(id=987654321);
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
Tahir
|
