Creating a unique date of service column

[wheddingsjr]

Hello all

I run a SAS query and when I get the data set I export it to excel and manually manipulate the data. I also use formulas to create new columns in the dataset. I was hoping someone would be able to help me do that within my SAS query if its possible that it can be done.

In the attached spreadsheet, there are two columns (light colored headings), that I add to the other two columns. The UNIQ DOS column was created by using the excel formula =IF(D1=D2,0,1).  It shows when the BEGDATE changes within the same MEMNO grouping. If its the same date as the date above it within the same MEMNO grouping it populates the cell with a 0, if the BEGDATE changes within the same MEMNO grouping it populates the cell with a 1.

The column labeled DATE COUNT does a cumulative count of all the 1's within the MEMNO grouping. That number is ascertained using the excel formula =COUNTIF($A$2:A2,A2). Can these formulas and data manipulations be done within the SAS query?

Any assistance would be greatly appreciated.

Thanks

[wheddingsjr]

By the way, before I use the DATE COUNT formula, I remove all the rows that have a 0 in the UNIQ DOS column then add them back agter I get the DATE COUNT

[Reeza]

---

@wheddingsjr wrote:

Can these formulas and data manipulations be done within the SAS query?

---

Yes, those can be accomplished in several different ways, but you need to think about it differently. Specifically, SAS has BY group processing that distinguishes blocks of data from each other if your groups are clearly defined.

You basically want this type of logic to identify your first of each date, but depending on what you're doing with that, maybe you don't want it at all, since the data step has automatic variables that do that. 

This tutorials walks through how to create the enumeration values and the last example uses two variables, which in your case is the ID and date. You likely need to program for this, I'm not aware of a task or GUI that will accomplish this in either SAS Studio or EG. 

https://stats.idre.ucla.edu/sas/faq/how-can-i-create-an-enumeration-variable-by-groups/

If you still have trouble post your code. 

[wheddingsjr]

Hi Reeza

Thanks for responding. I looked at the tutorial and if I understand it correctly, I think the example would definitely work for the DATE COUNT column. However, before doing so, I still need a way to populate the UNIQ DOS column to determine if the row should contain a 1 or a zero in that column because I want to exclude those rows with 0 when I do the count. In my initial post I neglected to include that but subsequently appended the post. The UNIQ DOS column shows when the BEGDATE changes within the same MEMNO grouping. If it’s the same date as the date above it within the same MEMNO grouping it populates the cell with a 0, if the BEGDATE changes within the same MEMNO grouping it populates the cell with a 1.

Thanks

[Reeza]

That would be the FIRST.DATE value, which you could store in your variable.

unique_dos = first.date;

---

@wheddingsjr wrote:

Hi Reeza

Thanks for responding. I looked at the tutorial and if I understand it correctly, I think the example would definitely work for the DATE COUNT column. However, before doing so, I still need a way to populate the UNIQ DOS column to determine if the row should contain a 1 or a zero in that column because I want to exclude those rows with 0 when I do the count. In my initial post I neglected to include that but subsequently appended the post. The UNIQ DOS column shows when the BEGDATE changes within the same MEMNO grouping. If it’s the same date as the date above it within the same MEMNO grouping it populates the cell with a 0, if the BEGDATE changes within the same MEMNO grouping it populates the cell with a 1.

Thanks

---

[wheddingsjr]

Before I was aware you responded, I was experimenting with one of the examples in the tutorial and used the following after creating a new column called combo which combines the MEMNO and the BEGDATE and used:

data Results1;

set Results;

count + 1;

by combo;

if first.combo then UNIQDOS = 1;

run;

I was pleasantly suprised that the results came out with the UNIQ DOS correctly but the count was a continuos count and did not restart at the new MEMNO (see attached).

[Reeza]

That's because MEMNO is not in your BY statement and you didn't set it to reset at the boundary....

data Results1;

set Results;

by memno combo;

if first.memno then count=0; *reset for memno;

count + 1;

UNIQDOS = first.combo;


run;

[wheddingsjr]

That worked perfectly!! Thanks Reeza. Now I have to work on the second stage which is to eliminate all the MEMNO's that have less than a 20 count.

[wheddingsjr]

I take that back..it didnt work perfectly. It should only count the UNIQDOS in the MEMNO grouping not the number of lines within the MEMNO grouping. There are 4 lines in a grouping but only 2 UNIQDOS, etc.

[Reeza]

Show the exact code you ran and the unexpected output please.

[Reeza]

proc sql;

create table demo as

select memno, count(distinct combo) as unique_dates

from have

group by memno;

quit;



proc sql;

create table demo as

select memno, count(distinct combo) as unique_dates

from have

group by memno

having calculated unique_dates> 20;

quit;

Those are some ways to get the list of records that are over 20

You can then exclude them using:

proc sql;
create table want as
select *
from have where memno  not in (select memno from demo);
quit;

[wheddingsjr]

This is the code I ran and I attached an excel file

data Results1;

set Results;

by memno combo;

if first.memno then count=0;

count + 1;

UNIQDOS = first.combo;

run;

[wheddingsjr]

The attached on the above post is the desired outcome

[Reeza]

So exactly what is different? I'm not seeing what's wrong with the solution.

EDIT:

Remember that many users, myself included, do not download excel files. I can view the preview but not download the files. So if you post data we can work with that helps, and Excel files are not workable. So the solutions provided are untested unless you provide sample data as a data step.

Instructions on how to provide sample data is here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[wheddingsjr]

I will try to post it on here

MEMNO	COUNT	UNIQDOS	BEGDATE
1234	1	1	02/16/18
2345	1	1	02/05/18
2345		0	02/05/18
2345	2	1	03/26/18
2345		0	03/26/18
3456	1	1	03/08/18
4567	1	1	03/07/18
4567		0	03/07/18
4567	2	1	03/29/18
4567		0	03/29/18
5678	1	1	03/08/18
5678	2	1	03/11/18
6789	1	1	01/08/18
6789		0	01/08/18
7890	1	1	03/22/18
7890		0	03/22/18
7890	2	1	04/11/18
7890		0	04/11/18

Its coming out as:

MEMNO	COUNT	UNIQDOS	BEGDATE
1234	1	1	02/16/18
2345	1	1	02/05/18
2345	2	0	02/05/18
2345	3	1	03/26/18
2345	4	0	03/26/18
3456	1	1	03/08/18
4567	1	1	03/07/18
4567	2	0	03/07/18
4567	3	1	03/29/18
4567	4	0	03/29/18
5678	1	1	03/08/18
5678	2	1	03/11/18
6789	1	1	01/08/18
6789	2	0	01/08/18
7890	1	1	03/22/18
7890	2	0	03/22/18
7890	3	1	04/11/18
7890	4	0	04/11/18