## How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

Solved
Occasional Contributor
Posts: 15

# How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

Hi Guys,

I need to find out Total Number of days each user has worked, Kindly tell me how i can do it using BASE SAS & PROC SQL.

Waiting for a response.

Thank you

Accepted Solutions
Solution
‎05-21-2017 11:01 AM
Super User
Posts: 24,026

## Re: How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

@Jagadishkatam the SQL can be simplified

``````select username, count (distinct date_worked) as unique_days
``````

All Replies
Super User
Posts: 24,026

## Re: How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

[ Edited ]

See the examples here:

http://support.sas.com/kb/25/279.html

Or I would recommend PROC MEANS

AgentA wrote:

Hi Guys,

I need to find out Total Number of days each user has worked, Kindly tell me how i can do it using BASE SAS & PROC SQL.

Waiting for a response.

Thank you

Occasional Contributor
Posts: 15

## Re: How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

Any way to do it from Base Sas? using first.id/last.id.
I need the exact code to do it.

Thanks
Posts: 1,169

## Re: How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

``````proc sort data=have nodupkey;
run;

data want;
set have;
retain count;
else count=count+1;
run;``````
Thanks,
Jag
Occasional Contributor
Posts: 15

## Re: How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

This one is not working since there are sames dates the user have worked, so if i apply a nodupkey then the same dates get deleted. So i do not get the accurate result.

The proc sql works , if its possible to do it by base sas then pl let me know.

Thank you for your time Jagdish.

Super User
Posts: 24,026

## Re: How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

AgentA wrote:

This one is not working since there are sames dates the user have worked, so if i apply a nodupkey then the same dates get deleted. So i do not get the accurate result.

The proc sql works , if its possible to do it by base sas then pl let me know.

Thank you for your time Jagdish.

No. If you need number of days worked and you have duplicate dates that does not mean they worked two days. It may mean two shifts but not two days. Based on your question, as stated, these answers are correct.

If you need the the number of entries rather than number of days it's a different question, and I would recommend PROC FREQ instead. Use the correct tools for the job. I suspect this is likely homework which is why you need a specific method.

Posts: 1,169

## Re: How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

Alternatively by proc sql

``````proc sql;
quit;
``````
Thanks,
Jag
Solution
‎05-21-2017 11:01 AM
Super User
Posts: 24,026

## Re: How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

@Jagadishkatam the SQL can be simplified

``````select username, count (distinct date_worked) as unique_days
``````
Occasional Contributor
Posts: 15

## Re: How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

Occasional Contributor
Posts: 15

## Re: How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

Hi,
I mean to say
If Nitin worked on 2 May 2014
Also Abhishek worked on 2 May 2014 then if i use nodupkep on days_worked then i will not get accurate results. Though proc sql is the best way to solve it , just wanted to know if it can be done using base sas.
Thank you so much for your time.
Super User
Posts: 24,026

## Re: How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

AgentA wrote:
Hi,
I mean to say
If Nitin worked on 2 May 2014
Also Abhishek worked on 2 May 2014 then if i use nodupkep on days_worked then i will not get accurate results. Though proc sql is the best way to solve it , just wanted to know if it can be done using base sas.
Thank you so much for your time.

That's not correct again. Don't make assumptions, check the data and results.

The NODUPKEY was used with the BY variables of PERSON + DAYS_WORKED so a duplicate day will not be erased unless it has multiple entries for the same person.

You can solve it with BASE SAS, but BASE SAS doesn't have a definition that you're seeming to use. PROC SQL is in BASE SAS, are you trying to say a DATA STEP? Because even then, there are better BASE SAS methods to use than a data step for a summary procedure.

Occasional Contributor
Posts: 15

## Re: How to find the count using BASE SAS & PROC SQL in given scenario (FILE ATTACHED)

Okay ! Got it . Thank you. Will check again all the things.
☑ This topic is solved.