BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hermina
Fluorite | Level 6

Hi,

I am very new to SAS programming (Enterprise Guide) and have tried combining procedures like Proc SQL, Proc Freq as well as macros, but no code worth posting here because they are full of errors. I would greatly appreciate any help! 

 

Input:

I have been given a dataset consisting of 8000 observations (really there are 80million, but that's not important for the context). Sample below.

ID is a variable but is not needed in the output.

For each observation ONE of the obs_1, obs_2 or obs_3 can have the value of 1, OR they are all 0.

 

Obsdata_periodIDEURriskgradeobs_1obs_2obs_3
1200801a203010
2200801b303010
3200801c504000
4200802a403100
5200803a204000
6200803c104100
80000201512c74001

 

Desired output:

Sample below (made in Excel).

I need one table for EACH riskgrade (in the real dataset there are 20 different, i.e. I want 20 tables).

Each data_period should be assigned to one row.

 

 

OBS with riskgrade 3
data_periodSUM_obs_1SUM_obs_2SUM_obs_3# of observationsSUM of EUR during data_period
200801020250
200802100140
Total (in sample)221390

 

OBS with riskgrade 4
data_periodSUM_obs_1SUM_obs_2SUM_obs_3# of observationsSUM of EUR during data_period
200801000150
200803100230
20151200117
Total (in sample)101487
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

PROC SUMMARY should do this.

 

Untested code

 

proc summary data=whatever;
    class riskgrade data_period;
    var eur obs_1 obs_2 obs_3;
    output out=want sum=sum_eur sum_obs_1 sum_obs_2 sum_obs_3 n(eur)=n;
run;
--
Paige Miller

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

PROC SUMMARY should do this.

 

Untested code

 

proc summary data=whatever;
    class riskgrade data_period;
    var eur obs_1 obs_2 obs_3;
    output out=want sum=sum_eur sum_obs_1 sum_obs_2 sum_obs_3 n(eur)=n;
run;
--
Paige Miller
hermina
Fluorite | Level 6

@PaigeMiller Wow, many thanks for your quick response!

I tried out your simple yet effective code on my sample data and it works like a charm.

Will give it a go on my real data tomorrow.

qxiaowen
Calcite | Level 5

Hi there,

I have a similar issue to aggregate my data file. I tried your code, but not apply to my data. I wonder if you can help me out as well. 

This is my data: 

COHORT_YEARFG_StatusNeedy_StatusTime2DegreeLocalRaceGendercount
2008NNNot graduatedAmerican Indian or Alaska NatF1
2009NNWithin5YearsAmerican Indian or Alaska NatM1
2008NY4YorLessAmerican Indian or Alaska NatF1
2010NY4YorLessAmerican Indian or Alaska NatM1
2008NY4YorLessAmerican Indian or Alaska NatF1
2012NY4YorLessAmerican Indian or Alaska NatM1
2008NY4YorLessAmerican Indian or Alaska NatF1
2008NY4YorLessAmerican Indian or Alaska NatM1

I have 15000 cases and I need to sum/aggregate the Headcount variable given all the 6 categorical variables, which means the output data keeps the categorical variables, and can be used in Excel pivot tables. As I am learning SAS not for long and tried many ways, but none of them works for me. Hope that you can help me out. Thanks in advance. 

Xiaowen Qin

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 10031 views
  • 1 like
  • 3 in conversation