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

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

1 ACCEPTED SOLUTION

Accepted Solutions
wheddingsjr
Pyrite | Level 9

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

22 REPLIES 22
wheddingsjr
Pyrite | Level 9

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
Super User

@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
Pyrite | Level 9

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
Super User

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
Pyrite | Level 9

 

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
Super User

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
Pyrite | Level 9

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
Pyrite | Level 9
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
Super User

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

Reeza
Super User

 

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
Pyrite | Level 9

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
Pyrite | Level 9
The attached on the above post is the desired outcome
Reeza
Super User

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
Pyrite | Level 9

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

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
  • 22 replies
  • 1243 views
  • 0 likes
  • 3 in conversation