9/8/2023 0 Comments Sqlpro run loopIn both cases I would use a HASH to accumulate the distinct combinations of:Īnd post process to get the account count per segment & position Use many statements ( macro generated) with bit mask test expressions to locate the rising edge at each place a 01 could occur when the month flags are organized as a collection of bits. In signal processing this is a rising edge and suggests the second way. Use arrays to organize segment and flag variables and compare flag() to flag() in a loop to detect each case of month to month flags transitioning from 0 to 1. Suppose the abc_* variable names are changed to flag_*. Notice how there is no value for COUNT_3 for SEGMENT=Y, since Y never appeared in SEG3 in the sample input. Results: Obs segment count_1 count_2 count_3 proc transpose data=step2 prefix=count_ out=want(drop=_name_) Then to get a dataset in the format you show we just need to transpose that. Now we can add up how many COUNT_ME observations there are per SEGMENT*MONTH. Keep date actt month segment current next count_me We can use a view so that we don't need to permanently store the vertical dataset. Since it looks like for month 3 you need to look at SEGMENT_3, ABC_4 and ABC_5 then you will get N-2 fewer months out than you have in the data. First let's convert your pasted listing into actual data. So let's take a shot at it and see if this is what you mean. It is still not at all clear what your algorithm is. Appreciate if someone could suggest a data step code as an alternative too! Note: tried transposing my data but it has over 41 million rows. Ideally, the combined results (using Segment as the unique identifier) should look like this: Segment Count_1 Count_2 Count_3 Would appreciate the help on how to fix my failed code below: %macro loop(a,b) code to run and to combine all months after. However, I would like to embed a macro as I have 84 months ie. Select distinct seg_3 as segment, (count(acct)) as count_3 Select distinct seg_2 as segment, (count(acct)) as count_2 Select distinct seg_1 as segment, (count(acct)) as count_1 The idea is that I want to count the number of accounts if it satisfies the condition of abc_(t)=0 and abc_(t+1)=1 for each segment columns.īelow is my code that gives me the results I wanted without looping: proc sql I have a dataset that looks like this (1 to 5 are months): date acct seg_1 seg_2 seg_3 seg_4 seg_5
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |