topic Re: How to sum up the value of columns and input this value in another table? in SAS Programming

How to sum up the value of columns and input this value in another table?

Muyu — Tue, 31 Jan 2017 15:25:24 GMT

Hi there,

I have a problem in sas programming.

What I need to do is first to count how many custs in total in Yin.Accounts_final_sb file. In other words, I need to add all the numbers in custs colums, and there are more than 150k columns.

Secondly, after getting the number of total custs, I will need to create a new column called custs and input the number under below in Yin.Pacs_all file.

Anyone can help me?

Thank you

Re: How to sum up the value of columns and input this value in another table?

Shmuel — Tue, 31 Jan 2017 15:56:06 GMT

try code:

proc sql;

select count(distinct custs) into : cnt

from Yin.Accounts_final_sb;

quit;

data Yin.Pacs_all;

set Yin.Pacs_all;

retain cnt_cust &cnt;

run;

Re: How to sum up the value of columns and input this value in another table?

ballardw — Tue, 31 Jan 2017 16:00:15 GMT

Some clarification:

You say " count how many custs in total " and "add all the numbers in custs colums". Do want a count or sum? "Add" implies addition or sum of numbers.

Also for a count do you want to count rows or unique number of values for Custs?

You say "after getting the number of total custs, I will need to create a new column called custs and input the number under below in Yin.Pacs_all ". Does that Yin.Pacs_all already exist? You don't show a variable custs so this is somewhat confusing. If there is no existing variable custs in the data set then you will need to create a new data set as adding variables after a set is created is not one of SAS's features. You could rename the data set after combining the values.

To clarify the problem it helps to post example data as a data step with the variables concerned and then show a desired result for the example data. All you need a is few rows that you can calculate by hand to show the desired result. Since the only variable I see you asking about from your existing data is custs the example data step code to create an example set could be this:

data have;
   input custs;
datalines;
149
150
151
151
152
;
run;

And then you ask for a count of custs=5, or a count of unique cust values=4, that you need to combine into an existing data set. We need to know which type of count you want to do to create the correct summary.

To combine with an existing data set of one record it may be as simple as this (summarydata is a data set that has the correct count of custs)

data want;
   merge Yin.Pacs_all
       summarydata
   ;
run;

Depending on which count you want here are two ways:

proc sql;
   create table summarydata as
   select count(custs) as custs 
   from Yin.Accounts_final_sb;
quit;
/* for count of unique cust values*/
proc sql;
   create table summarydata as
   select count(custs) as custs 
   from (select distinct custs from Yin.Accounts_final_sb);
quit;

Re: How to sum up the value of columns and input this value in another table?

Muyu — Tue, 31 Jan 2017 16:03:32 GMT

Seems it does not work...

To be specific, what I want is to add a column like below.

Thank you

Re: How to sum up the value of columns and input this value in another table?

Shmuel — Tue, 31 Jan 2017 16:07:55 GMT

All you need is a small change in 2nd step.

If you want to count rows dismiss distinct in 1st step.

proc sql;

select count(distinct custs) into : cnt

from Yin.Accounts_final_sb;

quit;

data Yin.Pacs_all;

set Yin.Pacs_all;

retain custs &cnt;

run;