BookmarkSubscribeRSS Feed
Q1983
Lapis Lazuli | Level 10

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

4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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?

ballardw
Super User

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.

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

novinosrin
Tourmaline | Level 20

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. 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 673 views
  • 0 likes
  • 5 in conversation