BookmarkSubscribeRSS Feed
deleted_user
Not applicable
How do I count the values of a variable for a particular customerid?
Example;
I need to count the value of void & smoke for distinct customers

Customerid void smoke
12001 1 2
12001 1 4
12001 1 3
12004 1 2
12004 1 4
12005 1 3
12005 1 3
12005 1 2
12006 1 4
12006 1 3
12007 1 2
12007 1 4
12007 1 3
12007 1 3

I have around 9999 obs in the datasets.
I tried PROC SQL but didn't get expected results.
Any help would be appreciated.
Thanx.
Priya
8 REPLIES 8
deleted_user
Not applicable
hai priya,

i saw your post. as per my knowledge if you exactly want the count od void and smoke for each id. then in sas, as of my knowledge several procedures are there.
the one way came to my mind is you can run

proc tabulate procedure .
proc tabulate data=;
class id void smoke;
table id, (void smoke) n;
run;

if any others answers please let us know.

thanks,
bhav
deleted_user
Not applicable
Hi,

If such a requirement comes to me, I try to solve it using data step. You can get the desired result by using retain statement. Consider the total of void and total of smoke as tvoid and tsmoke.

I created a datatset using the values you have given and named it 'New', and sorted it by custid.

DATA new1 (DROP=void smoke);
SET new;
BY custid;
RETAIN tvoid tsmoke;
IF first.custid THEN DO;
tvoid=.;
tsmoke=.;
END;
tvoid+void;
tsmoke+smoke;
IF last.custid THEN OUTPUT;
RUN;

I have not worked with a dataset with as many obs as yours, and whenever I need a cumulative value, I use retain.

The output I got has the structure:

Custid tvoid tsmoke
12001 3 9
12004 2 6
12005 3 8
12006 2 7
12007 4 12

Hope this is of use to you!!

Cathy
Cynthia_sas
SAS Super FREQ
Hi:
Proc FREQ will give you a frequency and cum frequency (as well as percent and cum percent) and if you don't like the format of the default FREQ output, you can always create an output dataset from PROC FREQ and use a different procedure to print the output dataset.

In addition to Proc TABULATE, Proc REPORT would do a count such as you want.

cynthia
deleted_user
Not applicable
datasets do have some null values, so how to handle them adding up. I am really stuck up here.
deleted_user
Not applicable
Finally got to find out the soultion of using sum(of function which considers missing values. This solved the problem.
Thanks for all your helping tips.
deleted_user
Not applicable
hai could you please write the program for you problem and post it back
deleted_user
Not applicable
Hi,

Sorry I could not respond to you immediately. Since all the variable values were present in the data you provided, I used the sum statement. We face this (propogation of null values) problem with sum statement, and thus it is safe to use sum function in case of missing values.

Cathy
deleted_user
Not applicable
Thanks for your help.
It did helped me. But later noticed that dataset has null values, which gives the sum as null values too. So how to handle adding up values along with null values.
This shld give a compleet solution.

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!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 8 replies
  • 1289 views
  • 0 likes
  • 2 in conversation