BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ltwo
Fluorite | Level 6

Hi SAS experts, I have a dataset that shows something like this:

 

 

data SASHELP.SAMPLE;
  infile datalines dsd truncover;
  input DATE:DDMMYY10. PERSON:$4. TASK:$4.;
  format DATE date9.;
datalines4;
01/01/2017,1000,AB
01/01/2017,1000,ABC
01/01/2017,1111,ABCD
01/01/2017,1111,ABC
01/01/2017,2111,ABCD
08/01/2017,1000,XY
08/01/2017,2111,ABC
08/01/2017,2111,XYZ
15/01/2017,1000,ABC
15/01/2017,1000,ABCD
15/01/2017,1000,AA
22/01/2017,1111,ABC
22/01/2017,1000,ABC
;;;;

What I'm basically looking for, is to identify PERSONs that have recorded an observation in each of the 4 dates that I have. 

 

So as we can see from the dataset above, only PERSON="1000" satisfies my requirement. So I want my eventual dataset to only contain PERSONs that meets the requirement.

 

data SASHELP.WANT;
 infile datalines dsd truncover;
  input DATE:DDMMYY10. PERSON:$4. TASK:$4.;
  format DATE date9.;
datalines4;
01/01/2017,1000,AB
01/01/2017,1000,ABC
08/01/2017,1000,XY
15/01/2017,1000,ABC
15/01/2017,1000,ABCD
15/01/2017,1000,AA
22/01/2017,1000,ABC
;;;;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User



data SAMPLE;
  infile datalines dsd truncover;
  input DATE:DDMMYY10. PERSON:$4. TASK:$4.;
  format DATE date9.;
datalines4;
01/01/2017,1000,AB
01/01/2017,1000,ABC
01/01/2017,1111,ABCD
01/01/2017,1111,ABC
01/01/2017,2111,ABCD
08/01/2017,1000,XY
08/01/2017,2111,ABC
08/01/2017,2111,XYZ
15/01/2017,1000,ABC
15/01/2017,1000,ABCD
15/01/2017,1000,AA
22/01/2017,1111,ABC
22/01/2017,1000,ABC
;;;;

proc sql;
create table want as
 select *
  from sample
   group by person
    having count(distinct date)=
(select count(distinct date) from sample)
order by person,date;
quit;

View solution in original post

5 REPLIES 5
LinusH
Tourmaline | Level 20
If your real data is not huge, I would first query how many dates that you have, and store that as a macro variable (SQL SELECT INTO:...).
Then the next query with a group by on Person, and having count (date) = &yourmacrovarcreatedinthepreviousstep.

Can a person gave multiple entries for a given date?
Data never sleeps
Ltwo
Fluorite | Level 6

Unfortunately the real data is huge, with millions of rows. The date is always a Sunday (not sure if this is something I can work off?) and represents the week starting (so e.g. week starting Sunday 01/01/2017). So let's say that I would like to see how many PERSONs have observations every Sunday for a particular period (e.g. 50 or 100 weeks).

 

A PERSON can have multiple entries a given date.

Kurt_Bremser
Super User

You can do it in a single SQL step, but be aware that the performance might be bad if your dataset is large:

proc sql;
create table want as
select a.*
from sample a
where a.person in (
  select b.person
  from sample b
  group by b.person
  having count(distinct date) >= 4
);
quit;
gamotte
Rhodochrosite | Level 12

Hello,

 

I don't think you need a subrequest here.

 

proc sql noprint;
	CREATE TABLE want AS
	SELECT *
	FROM SAMPLE
	GROUP BY PERSON
	HAVING count(DISTINCT DATE) ge 4;
quit;
Ksharp
Super User



data SAMPLE;
  infile datalines dsd truncover;
  input DATE:DDMMYY10. PERSON:$4. TASK:$4.;
  format DATE date9.;
datalines4;
01/01/2017,1000,AB
01/01/2017,1000,ABC
01/01/2017,1111,ABCD
01/01/2017,1111,ABC
01/01/2017,2111,ABCD
08/01/2017,1000,XY
08/01/2017,2111,ABC
08/01/2017,2111,XYZ
15/01/2017,1000,ABC
15/01/2017,1000,ABCD
15/01/2017,1000,AA
22/01/2017,1111,ABC
22/01/2017,1000,ABC
;;;;

proc sql;
create table want as
 select *
  from sample
   group by person
    having count(distinct date)=
(select count(distinct date) from sample)
order by person,date;
quit;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 796 views
  • 1 like
  • 5 in conversation