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?
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;
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.
Thanks a lot!
I have figured it out! The name is not structured.
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;
But my dataset name is not structured. They are named differently.
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.
"an indentifying variable for each dataset according to dataset name"
Variable DSN already plays that role in my answer.
Cool! Thanks! INDSNAME is the code. Thank you
Hello PG.
The INDSNAME=_ds_ i gives me the name variable with folder name.
How can I get rid of the folder name?
Replace
dsn = _ds_;
with
dsn = scan(_ds_, 2);
Check out the definition of the scan function in the documentation.
Thank you!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.