turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- How to find the count using BASE SAS & PROC SQL in...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-21-2017 04:00 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Jagadishkatam

05-21-2017 10:22 AM

@Jagadishkatam the SQL can be simplified

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AgentA

05-21-2017 04:39 AM - edited 05-21-2017 04:40 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

05-21-2017 06:36 AM

Any way to do it from Base Sas? using first.id/last.id.

I need the exact code to do it.

Thanks

I need the exact code to do it.

Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AgentA

05-21-2017 08:24 AM

```
proc sort data=have nodupkey;
by Username Day_Worked;
run;
data want;
set have;
by Username Day_Worked;
retain count;
if first.username then count=1;
else count=count+1;
if last.username;
run;
```

Thanks,

Jag

Jag

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Jagadishkatam

05-21-2017 11:04 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AgentA

05-21-2017 11:40 AM

AgentA wrote:

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AgentA

05-21-2017 08:31 AM

Alternatively by proc sql

```
proc sql;
create table want as select distinct count(username) as count,username from (select distinct username,Day_Worked from have) group by username;
quit;
```

Thanks,

Jag

Jag

Solution

05-21-2017
11:01 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Jagadishkatam

05-21-2017 10:22 AM

@Jagadishkatam the SQL can be simplified

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AgentA

05-21-2017 02:36 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AgentA

05-21-2017 02:37 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AgentA

05-21-2017 02:41 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to AgentA

05-21-2017 02:53 PM

Okay ! Got it . Thank you. Will check again all the things.