12-04-2017 10:12 AM
create table disaster1 as
sum(nu) as Number,
sum(dol) as Doll
group by Disaster,State,Investor
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
12-04-2017 10:20 AM
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?
12-04-2017 10:21 AM
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.
12-04-2017 10:29 AM
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.
12-04-2017 02:21 PM
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 object.dot notation methods - add, output and clear.