topic Re: Creating a unique date of service column in SAS Programming

Creating a unique date of service column

wheddingsjr — Mon, 21 May 2018 19:28:06 GMT

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

Re: Creating a unique date of service column

wheddingsjr — Mon, 21 May 2018 20:19:34 GMT

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

Re: Creating a unique date of service column

Reeza — Mon, 21 May 2018 21:56:03 GMT

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

Re: Creating a unique date of service column

wheddingsjr — Tue, 22 May 2018 14:23:20 GMT

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

Re: Creating a unique date of service column

Reeza — Tue, 22 May 2018 15:25:51 GMT

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

Re: Creating a unique date of service column

wheddingsjr — Tue, 22 May 2018 19:07:29 GMT

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

Re: Creating a unique date of service column

Reeza — Tue, 22 May 2018 19:38:16 GMT

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;

Re: Creating a unique date of service column

wheddingsjr — Tue, 22 May 2018 19:49:42 GMT

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.

Re: Creating a unique date of service column

wheddingsjr — Tue, 22 May 2018 19:53:50 GMT

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.

Re: Creating a unique date of service column

Reeza — Tue, 22 May 2018 19:57:46 GMT

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

Re: Creating a unique date of service column

Reeza — Tue, 22 May 2018 20:01:45 GMT

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;

Re: Creating a unique date of service column

wheddingsjr — Tue, 22 May 2018 20:22:29 GMT

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;

Re: Creating a unique date of service column

wheddingsjr — Tue, 22 May 2018 20:24:30 GMT

The attached on the above post is the desired outcome

Re: Creating a unique date of service column

Reeza — Tue, 22 May 2018 20:30:50 GMT

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-data-AKA-generate/ta-p/258712

Re: Creating a unique date of service column

wheddingsjr — Tue, 22 May 2018 20:45:36 GMT

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

Re: Creating a unique date of service column

Reeza — Tue, 22 May 2018 20:49:34 GMT

So the duplicates in the COUNT is what you don't want?

That seems weird and inconsistent with most other uses.

You would have to create a temporary variable and use the FIRST logic to assign the variable only at the start. But you'd have to keep the temporary variable as well. Basically, exactly what you have but add a new variable and that is the same but only assigned when it's the first for each date, or uniquedos=1.

Or you could process it after the fact to set it to missing whenever uniquedos=0.

Re: Creating a unique date of service column

wheddingsjr — Tue, 22 May 2018 21:02:15 GMT

That is correct. I am trying to tally all of the unique dates of service per MEMNO, NOT the total amount of lines per MEMNO

Re: Creating a unique date of service column

Reeza — Tue, 22 May 2018 21:17:27 GMT

The counts are still correct, just duplicated for duplicates.

If this is just for presentation by the way, using PROC REPORT or PRINT with a GROUP should prevent it from being duplicated.

Re: Creating a unique date of service column

wheddingsjr — Wed, 23 May 2018 19:50:37 GMT

Reeza

I was able to do everything I wanted to do with this and it came out perfect. I definately appreciate the help and what was once a 3-4 hour manual adventure (not counting an additional step that I will try to figure out), has now been reduced to 20 mins. so far. Next step is comparing my outcome to the previous month. I am going to search the net to see if there is a way to import documents into my SAS program.

Re: Creating a unique date of service column

Patrick — Thu, 24 May 2018 03:16:49 GMT

@wheddingsjr

"Next step is comparing my outcome to the previous month. I am going to search the net to see if there is a way to import documents into my SAS program."

Going forward consider to do ALL your calculations in SAS and then store the monthly data in a monthly permanent table. This way your source of truth will be the managed tables and you won't have to deal with importing some external documents/Excels over which you eventually don't have full control. ...and if you keep these monthly tables then you're always able to fully reproduce results at a later stage and of course can simply compare the data in different monthly tables to create movement reports or for whatever reasons you have to compare the months.