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

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.

 Captur11e.PNG

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.

Capture.PNG

Anyone can help me?

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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;

View solution in original post

4 REPLIES 4
Shmuel
Garnet | Level 18

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;

Muyu
Calcite | Level 5

Seems it does not work...

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

Thank you

Captur1e.PNG

Shmuel
Garnet | Level 18

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;

ballardw
Super User

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;

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
  • 692 views
  • 0 likes
  • 3 in conversation