SAS help: Rolling up information in SAS data


#1

Hi,

I have a question on how to do certain steps in SAS.

I have this data which is sorted by: Customer_ID Year Month Segment Cardtype Spend Region

If you look at Table 1, the first two rows have the same observations except of and TXN. I would like to sum up the and txn to get to Table 2.

Q: May I know how to do it in SAS? Is it using retain, first. and last. ?

Hope to get help from here. Thanks!


#2

Hi @Marisa_Adam,

Use proc summary for the same. Here is the code -

Proc summary data= table_1 nway noprint missing;
Class year month segment cardtype spend region customer_id ;
Var _numeric_;
Output out = table_2(drop= _type_  _freq_) sum=;
Run;

Hope this helps.

Regards,
Aayush


#3

Hi Aayush,

Thanks for your reply. I would write something like this:

Proc summary data= table_1 nway noprint missing;
Class year month segment cardtype spend region customer_id ;
Var txn; Output out = table_2(drop= _type_ _freq_) sum()=sum_$
sum(txn)=sum_txn;
Run;

so does writing numeric would call all the numeric variables instead of typing the variable one by one in after function var ?


#4

Hi @Marisa_Adam,

Yes writing “numeric” will save you from writing all the numeric variable one by one :smile:

Regards,
Aayush


#5

Thank you so much Aayush! :smile:

I have one more question, sorry.

Table 3

If you look at the table above, is there a possible way I can pivot them in Excel by keeping the number of customers? As you can see, one customer (Cust_ID) can perform transaction in different month, under different segment, with different cardtype, can be both online or retail spending, and performed transaction either locally or overseas. Therefore there can be millions of observations and I can’t export this to Excel.

Is there actually a way to keep the number of unique rows after rolling up to customer_id level (in my first question)? The reason why is because I need to remove the cust_id (sensitive information) later and just show the number of customers in order to do pivoting. Does my question make sense? Hehe sorry.

I might be overlooking something…


#6

HI @Marisa_Adam,

I think I understand the concept. Let me re-state it for you -

You want to keep the count of rows rolling up, while actually rolling up the data. How you can do this by initially create a counter column with all entries as 1. Then do a proc summary and but the counter in variable. You will end up with the same.

Regards,
Aayush


#7

So you are saying that it’s not relevant to do pivoting right as there are so many layers? Am I right?


#8

Hi Marisa,

I think you don’t need to do pivoting to do this task. It can be done in sas itself.

Regards,
Aayush