topic Re: How to sum variable based on other variables for 1000 dataset using MACRO in SAS Programming

How to sum variable based on other variables for 1000 dataset using MACRO

yanshuai — Thu, 01 Mar 2018 18:58:24 GMT

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?

Re: How to sum variable based on other variables for 1000 dataset using MACRO

mkeintz — Thu, 01 Mar 2018 03:53:06 GMT

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.

Re: How to sum variable based on other variables for 1000 dataset using MACRO

PGStats — Thu, 01 Mar 2018 04:06:03 GMT

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;

Re: How to sum variable based on other variables for 1000 dataset using MACRO

yanshuai — Thu, 01 Mar 2018 18:54:31 GMT

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

Re: How to sum variable based on other variables for 1000 dataset using MACRO

yanshuai — Thu, 01 Mar 2018 19:00:08 GMT

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.

Re: How to sum variable based on other variables for 1000 dataset using MACRO

yanshuai — Thu, 01 Mar 2018 19:01:34 GMT

Thanks a lot!

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

Re: How to sum variable based on other variables for 1000 dataset using MACRO

PGStats — Thu, 01 Mar 2018 19:05:50 GMT

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

Variable DSN already plays that role in my answer.

Re: How to sum variable based on other variables for 1000 dataset using MACRO

yanshuai — Thu, 01 Mar 2018 19:12:02 GMT

Cool! Thanks! INDSNAME is the code. Thank you

Re: How to sum variable based on other variables for 1000 dataset using MACRO

yanshuai — Thu, 01 Mar 2018 20:42:40 GMT

Hello PG.

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

How can I get rid of the folder name?

Re: How to sum variable based on other variables for 1000 dataset using MACRO

PGStats — Thu, 01 Mar 2018 20:50:32 GMT

Replace

dsn = _ds_;

with

dsn = scan(_ds_, 2);

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

Re: How to sum variable based on other variables for 1000 dataset using MACRO

yanshuai — Thu, 01 Mar 2018 22:04:42 GMT

Thank you!