How to sum variable based on other variables 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.

Thank you!

 ---------------------------------------------------------------------------------------------------updated 02-28-2018

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 I add another variable for each dataset accoeding to its file name?

[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.

Thank you!

 ---------------------------------------------------------------------------------------------------updated 02-28-2018

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 I add another variable for each dataset accoeding to its file name?

[mkeintz]

One thousand data sets?  Do they have a common name structure?  If they do (say they all are start with ABC, as in ABC001 ABC002 ABCxyz ABC999), and if they all have the same variables, then you could

data need/ view=need;
   set abc:   open=defer;
run;

Then run your proc sql using NEED instead of HAVE.

It will read all the dataset files ABCxxx ABCyyy ABC... etc, into a data set VIEW named NEED.   But that won't happen until NEED is used in a subsequent procedure.  As a result NEED will not be written to disk, but instead fed directly into PRCO SQL, saving lots of disk activity and space.

If they don't have a simple name structure, you could still make a view called NEED, but the SET statement will have more arguments ... lots more.

[yanshuai]

Thanks a lot!

I have figured it out! The name is not structured.

[PGStats · Accepted solution]

You could do it all at once using a dataset list, without macro processing. Suppose your datasets are called SET1, SET2, ... SET1000 in Library myLib, you could do:

data allMyData;
length dsn $41;
set myLib.set1-myLib.set1000 open=(defer) indsname=_ds_;
dsn = _ds_;
run;

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

[yanshuai]

But my dataset name is not structured. They are named differently.

[yanshuai]

But anyways, I have figured it out!!! Thank you!

Next issue is how to add an indentifying variable for each dataset according to dataset name. I will post another question.

[PGStats]

"an indentifying variable for each dataset according to dataset name"

Variable DSN already plays that role in my answer.

[yanshuai]

Cool! Thanks! INDSNAME is the code. Thank you

[yanshuai]

Hello PG.

The INDSNAME=_ds_ i gives me the name variable with folder name.

How can I get rid of the folder name?

[PGStats]

Replace

dsn = _ds_;

with

dsn = scan(_ds_, 2);

Check out the definition of the scan function in the documentation.

[yanshuai]

Thank you!