Creating a unique date of service column

[Reeza]

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.

[wheddingsjr]

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

[Reeza]

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.

[wheddingsjr · Accepted solution]

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.

[Patrick]

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

[wheddingsjr]

Thanks Patrick

That is an excellent idea. But for the first time I imported the previous month. I am looking at a page online which shows how to compare the two tables, but it just highlighting the lines on the new table that do not exist on the old. I want to be able to compare the lists and if a line item is on the old table I want a prexisting column in the new table to populate with "Y" or "N". Is that possible or would that have to be done manually?

[Reeza]

There are multiple ways to do comparisons of month over month, if that’s what your’e trying to do. 

However, this is a new question, and I’d strongly suggest a new thread.  To date I have not found very much I could do in Excel and not SAS, most superficially, SAS is limited in controlling where the output goes in an Excel file, but otherwise I’ve migrated a ton of Excel projects to SAS to save time and my sanity. 

[wheddingsjr]

Thanks Reeza

Thats exactly what I am trying to do. I will close this out and start a new one. Thanks for all your help.