topic Re: Separate datasets based on variables in SAS Programming

Separate datasets based on variables

Q1983 — Mon, 04 Dec 2017 15:12:56 GMT

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

Re: Separate datasets based on variables

PeterClemmensen — Mon, 04 Dec 2017 15:20:39 GMT

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?

Re: Separate datasets based on variables

ballardw — Mon, 04 Dec 2017 15:21:24 GMT

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.

Re: Separate datasets based on variables

RW9 — Mon, 04 Dec 2017 15:29:15 GMT

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.

Re: Separate datasets based on variables

novinosrin — Mon, 04 Dec 2017 19:21:21 GMT

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.