Count of first occurrence of Distinct accounts by Time Frame, Rolling Sum

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Count of first occurrence of Distinct accounts by Time Frame, Rolling Sum

data FLAGS;
input accountID 30Flag 60Flag 90Flag 120Flag 150Flag;
DATALINES;
1 OCT2014 - - - -
1 - NOV2014 - - -
1 DEC2014 - - - -
1 - JAN2015 - - -
2 - - - - -
2 OCT2014 - - - -
2 - - - - -
2 DEC2014 - - - -
3 - OCT2014 - - -
3 - - NOV2014 - -
3 - - - DEC2015 -
3 - - - - JAN2016
;
RUN;

 

Hi Guys, I'm new to SAS for about 5 weeks and need help. I know my data set above is wrong and if you can correct it, that'll be much appreciated. 

I'm also lookig to tally up the first occurence of a specific ID in a specific month for that specific certain flag.

Let's say for Account one, it is in 30flag in October and Dec2014.

I only want to count Account 1 as a tally for october, NOT December. And for its count to be tallied for November for 60Flag, but not for Jan2015 Flag. And I want a sum of all 30flags per month. And a rolling sum of each month or frequency table?

 

Best,

 


Accepted Solutions
Solution
a week ago
New Contributor
Posts: 2

Re: Count of first occurrence of Distinct accounts by Time Frame, Rolling Sum

I figured it out. Thanks guys.

View solution in original post


All Replies
Super User
Posts: 10,483

Re: Count of first occurrence of Distinct accounts by Time Frame, Rolling Sum

And the desired output would look like what? You threw in enough different counts that it isn't quite clear what you are counting.

 

If you have an existing data set start here for posting data: Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

 

I say that because your example data step probably isn't generating what you actually have. Unless "-" is a value in you data. In which case how to treat it.

 

One of the solutions to what I think you are asking would be to sort the data by account and flag, get the "first" one and then count the results. But wth your shown data I'm not sure if you have actual SAS date values, character values or what. So the sort may not work: Hint April comes before January if the values are character.

PROC Star
Posts: 1,561

Re: Count of first occurrence of Distinct accounts by Time Frame, Rolling Sum

This looks like garbled Excel data.

Please make into a SAS table, with SAS dates (example abound on how to do this).

Then you can manipulate the data as you wish, and provide it to us for helping you as suggested above.

 

Solution
a week ago
New Contributor
Posts: 2

Re: Count of first occurrence of Distinct accounts by Time Frame, Rolling Sum

I figured it out. Thanks guys.
☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 110 views
  • 0 likes
  • 3 in conversation