DATA Step, Macro, Functions and more

Separate datasets based on variables

Reply
Regular Contributor
Posts: 178

Separate datasets based on variables

proc sql;

create table disaster1 as

select Disaster,

          State,

          Investor,

         sum(nu) as Number,

         sum(dol) as Doll

from disaster

group by Disaster,State,Investor

;quit;

OUTPUT

Disaster State Investor Numb Doll
HURRICANE Buela TX FNMA 2 $206,650
HURRICANE Orange AL FNMA 0 $0
HURRICANE Orange FL FNMA 332 $61,817,366
HURRICANE Orange GA FNMA 6 $1,550,996
HURRICANE Orange SC FNMA 0 $0
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

PROC Star
Posts: 1,190

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,084

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,227

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.

PROC Star
Posts: 1,351

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 object.dot notation methods - add, output and clear. 

 

 

Ask a Question
Discussion stats
  • 4 replies
  • 111 views
  • 0 likes
  • 5 in conversation