DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

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 Smiley Happy


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

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

Posted in reply to Jagadishkatam

@Jagadishkatam the SQL can be simplified

 

select username, count (distinct date_worked) as unique_days

View solution in original post


All Replies
Super User
Posts: 19,785

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 Smiley Happy


 

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
Trusted Advisor
Posts: 1,137

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

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
Occasional Contributor
Posts: 15

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

Posted in reply to Jagadishkatam

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: 19,785

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. 

Trusted Advisor
Posts: 1,137

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

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
Solution
‎05-21-2017 11:01 AM
Super User
Posts: 19,785

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

Posted in reply to Jagadishkatam

@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: 19,785

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 390 views
  • 6 likes
  • 3 in conversation