How to sum variable based on condition for 1000 dataset using MACRO

[yanshuai]

Hello all,

 

I have a thousand dataset, and each of them like this:

 

data have ;
input
YEAR SALE ID $5.;
datalines;
2001  12  ab34
2001  10  1234
2001  14  ab34
2001  10  2234
2002  10  1234
2002  10  ab34
2002  10  2234
2002  10  2234
2001  12  ab34
2001  10  1234
2001  14  2234
2001  10  2234
2002  10  ab34
2002  10  1234
2002  10  ab34
2002  10  2234
;
run;

 

For each dataset, I would like to sum all the SALE winthin same YEAR whose ID start with a letter, and sum the SALE within same YEAR only. I know how to run this with the following code. Thanks to those guys in my another post

proc sql;
create table want as
select YEAR,
    sum(SALE*anyalpha(first(ID))) as SALE1,
    sum(SALE) as TOTALSALE
from have
group by YEAR;
quit;

_But how can I do this for each of 1000 dataset. It is impossible to do this manually. Really urgent. How can I repeat this using MACRO.
------------------------------------------------------------------------------------updated 8pm 02/28---------------------------------------------

I figure it out guys!!!!!!!!!!!!! This is the code I use:

%macro doit(memname);
proc sql;
create table want.&memname as
select YEAR,
    sum(SALE*anyalpha(first(ID))) as SALE1,
    sum(SALE) as TOTALSALE,
from have.&memname
group by YEAR;
quit;
%mend doit;

data _null_;
set work.member;      /* this is a list I created before hand, it contains all the member name of the datasets I have*/
call execute('%doit('!!memname!!');');
run;

Now I have the variables I want, but they all in individual dataset.

So the question now is how can add another variable for each dataset accoeding to its file name?

[PaigeMiller]

Forget about using PROC SQL to do this. Use PROC SUMMARY.

Concatenate all the data sets into a single data set with appropriate identification. Then run PROC SUMMARY on this.

To concatenate all the data sets into a single data set, you'd have to identify them somehow using file name or libname or other. Then you can extract all the data set names using PROC DATASETS, and then either using MACROs or CALL EXECUTE, create the single data step.

[yanshuai]

Thank you very much.

My logic is very simple. Just repeat SQL for each dataset. Maybe DATA step is more suitable for MACRO.

I just don't know how to illustrate this in MACRO.

[yanshuai]

I tried to concatenate all the datasets together using SET.

But seems the variable names are defined differently: has been defined as both character and numeric.

How can I do this?

[PaigeMiller]

So this is a problem of cleaning up the data, if you are going to SET them all together, and I'm afraid there's no easy way to do this with 1000 data sets. The only way I know of to force them to have all variables being the same (character or numeric) is to do it manually, one-by-one.

So maybe the solution is to run PROC SUMMARY 1000 times in a macro loop. But you haven't stated how to identify these data sets, do they have consecutive numbers as their name (example: data1, data2, data3, ... ) or are they all in a single folder??

[yanshuai]

Thank you. I have figured out how to do what I want using macro. Thanks.

They are all in the same folder but named quite unstructurally.

Next question is how to add an identifying variable for each dataset so that I can append them all togetherr.