Creating a unique date of service column

Accepted Solution Solved
Reply
Contributor
Posts: 59
Accepted Solution

Creating a unique date of service column

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


Accepted Solutions
Solution
‎05-24-2018 04:23 PM
Contributor
Posts: 59

Re: Creating a unique date of service column

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.

View solution in original post


All Replies
Contributor
Posts: 59

Re: Creating a unique date of service column

Posted in reply to 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

Super User
Posts: 23,667

Re: Creating a unique date of service column

Posted in reply to wheddingsjr

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

 

 

Contributor
Posts: 59

Re: Creating a unique date of service column

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

Super User
Posts: 23,667

Re: Creating a unique date of service column

Posted in reply to wheddingsjr

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


 

Contributor
Posts: 59

Re: Creating a unique date of service column

 

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

 

 

Super User
Posts: 23,667

Re: Creating a unique date of service column

Posted in reply to wheddingsjr

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;
Contributor
Posts: 59

Re: Creating a unique date of service column

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.

Contributor
Posts: 59

Re: Creating a unique date of service column

Posted in reply to 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.
Super User
Posts: 23,667

Re: Creating a unique date of service column

Posted in reply to wheddingsjr

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

Super User
Posts: 23,667

Re: Creating a unique date of service column

 

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;

 

Contributor
Posts: 59

Re: Creating a unique date of service column

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;

 

 

 

Contributor
Posts: 59

Re: Creating a unique date of service column

Posted in reply to wheddingsjr
The attached on the above post is the desired outcome
Super User
Posts: 23,667

Re: Creating a unique date of service column

[ Edited ]
Posted in reply to wheddingsjr

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

Contributor
Posts: 59

Re: Creating a unique date of service column

I will try to post it on here

 

MEMNOCOUNTUNIQDOSBEGDATE
12341102/16/18
23451102/05/18
2345 002/05/18
23452103/26/18
2345 003/26/18
34561103/08/18
45671103/07/18
4567 003/07/18
45672103/29/18
4567 003/29/18
56781103/08/18
56782103/11/18
67891101/08/18
6789 001/08/18
78901103/22/18
7890 003/22/18
78902104/11/18
7890 004/11/18

 

Its coming out as:

 

MEMNOCOUNTUNIQDOSBEGDATE
12341102/16/18
23451102/05/18
23452002/05/18
23453103/26/18
23454003/26/18
34561103/08/18
45671103/07/18
45672003/07/18
45673103/29/18
45674003/29/18
56781103/08/18
56782103/11/18
67891101/08/18
67892001/08/18
78901103/22/18
78902003/22/18
78903104/11/18
78904004/11/18
☑ This topic is solved.

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

Discussion stats
  • 22 replies
  • 136 views
  • 0 likes
  • 3 in conversation