DATA Step, Macro, Functions and more

Separate datasets based on variables

Regular Contributor
Posts: 180

Separate datasets based on variables

proc sql;

create table disaster1 as

select Disaster,



         sum(nu) as Number,

         sum(dol) as Doll

from disaster

group by Disaster,State,Investor



Disaster State Investor Numb Doll
HURRICANE Buela TX FNMA 2 $206,650
HURRICANE Orange FL FNMA 332 $61,817,366
HURRICANE Orange GA FNMA 6 $1,550,996
WILDFIRES CA FNMA 10 $2,638,711

I need to create separate datasets based on disaster and state.

I could do a datastep and define each state(hardcode) however if additional states are added later I would miss them.  How could I dynamically set up individual datasets based on desired criteria that will handle any states or disaster types not listed here.

I will eventually use the datasets in proc tabulate with totals based on disaster and state

Posts: 1,400

Re: Separate datasets based on variables

What does your disaster data set look like?


I don't fully understand. You post a desired output data set that contains different values for both disaster and state, but you want to create separate data sets for distinct values of these two variables? Or am I getting it wrong?

Super User
Posts: 13,941

Re: Separate datasets based on variables

Please show what datasets you would create from your given data.

You may have issues based on how/what you want to name things based on the 32 character limit on datasets.


Please describe how processing separate datasets is going to be an improvement over BY group processing.


If the sole purpose of the data sets is to create separate reports as needed you might be better off with a Where statement or dataset option at report time.



Super User
Super User
Posts: 9,840

Re: Separate datasets based on variables

Quite agree with @ballardw here.  Its is rarely a good idea to split up like data.  You are already hitting issues like how do I know all the groups?  How are you going to handle that in further code - what you will end up with is a whole load of unstable macro code trying to handle such a setup.  Keep it simple smart, use the tool how its supposed to be used.

Super User
Posts: 2,061

Re: Separate datasets based on variables

If i understand you,these kind of questions have been asked multiple times before. I gave a hash solution and I remember many others gave the same and SQL approach in a macro wrapper coz you need to work with %do and %if. Forgive me, I am too lazy to code and feeling sleepy, however here are simple steps:


1. take _null_ approach.

2. instantiate an empty hash table with all: keys(distaster and state) and define data

3. read the dataset in one pass with set

4. add your contents for each key multidata to the hash. output with dynamce hash output method and clear the contents of the hash to make way for the next by group

5. repeat the same and have fun

6. In essence, you need 3 hash notation methods - add, output and clear. 



Ask a Question
Discussion stats
  • 4 replies
  • 5 in conversation