DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

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

[ Edited ]

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?


Accepted Solutions
Solution
‎03-01-2018 02:01 PM
Esteemed Advisor
Posts: 5,391

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

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;

PG

View solution in original post


All Replies
Trusted Advisor
Posts: 1,284

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

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.

Contributor
Posts: 50

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

Thanks a lot!

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

Solution
‎03-01-2018 02:01 PM
Esteemed Advisor
Posts: 5,391

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

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;

PG
Contributor
Posts: 50

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

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

Contributor
Posts: 50

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

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.

Esteemed Advisor
Posts: 5,391

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

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

 

Variable DSN already plays that role in my answer.

PG
Contributor
Posts: 50

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

Cool! Thanks! INDSNAME is the code. Thank you

Contributor
Posts: 50

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

Hello PG.

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

How can I get rid of the folder name?

Esteemed Advisor
Posts: 5,391

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

Replace

 

dsn = _ds_;

 

with

 

dsn = scan(_ds_, 2);

 

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

PG
Contributor
Posts: 50

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

Thank you!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 142 views
  • 0 likes
  • 3 in conversation