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

[AgentA]

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 🙂

[Reeza]

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 🙂

---

[AgentA]

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

Thanks

[Jagadishkatam]

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;

[AgentA]

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. 

[Reeza]

---

@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. 

[Jagadishkatam]

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;

[Reeza · Accepted solution]

@Jagadishkatam the SQL can be simplified

select username, count (distinct date_worked) as unique_days

[AgentA]

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.

[Reeza]

---

@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. 

[AgentA]

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