tmmm, It looks like you have some confusion about the end result here.  There is no such thing as a single length-of-stay that applies to the entire MEMID.  You would need 12 lengths of stay, one for each ADMITDT.  While you could think about adding LengthOfStay to the VAR statement in PROC TRANSPOSE, it's simpler to eliminate the records entirely from your analysis data set.  Add this statement to your PROC TRANSPOSE step: where lengthofstay > 0; You'll be left with just the records you need to analyze.  If (as you say) your SAS skills are just coming up to speed,  you might even save the transposed data twice ... one as it is now, and once after adding the WHERE statement.  It depends on what you need to do with the data later.  Good luck. ... View more

podarum, Though some may disagree, I believe one of the primary purposes of this facility is to teach you to fish, rather than to present you with a basket of fish.  I think this is something you can do yourself, if you would try.  Just in case you need a hint or two, I'll spell out a little more below. There's no SQL in my solution, so you don't need to know any of that.  The existing variables N_STORE and N_STORE_SO_FAR can remain as they are.  No matter how many variables you want to add, the number of observations per STORE does not change.  For each variable that you add, you need to compute 3 more variables.  For example, to add a flag for VAR2, you would need to add VAR2_BEGINNING, VAR2_ENDING, and VAR2_FLAG.  It should be simple enough to repeat the logic that you see for VAR1.  If you really try and can't do it, post your progress and we can tweak it. Good luck. ... View more

podarum, So far so good, but you'll have to do more of the work than that.  How many flags would you like for each variable?  What values would you like them to take on?  Please illustrate. ... View more

HB, You've gotten a lot of good suggestions here.  In my view, here's where things stand. Ksharp's approach looks most workable, because it is PROC FREQ-based.  You had mentioned along the way that you might want some percentages instead of counts, where the percentage calculations omit missing values.  That's exactly how PROC FREQ works.  That would require significant changes to Ksharp's current version, but he (as well as several other posters) would be quite capable of doing that:  (1) sorting by ENTITY, (2) running PROC FREQ BY ENTITY, (3) selecting which variables require percentages and which require counts (you would provide the rules on that one), (4) if needed, adjusting percentages from a scale of 0 to 100 to a scale of 0 to 1, (5) reconstructing NAME, (6) reshaping the results so they are ready for PROC PRINT, and (7) getting the PROC PRINT output into Excel-ready format.  I know the steps are beyond your own SAS knowledge, but that would be taken care of for you.  You would need to supply the rules about which variables require percentages and which require counts.  The final PROC PRINT might need to add a VAR statement to group variables appropriately, such as: var con1_: con2_: con3_:; Down the road, you would have to watch out for new CON variables that take on unusual values. There could be a problem with the automation of variable names (the column headings), since SAS names are limited to letters, numbers, and underscores.  If CON20 takes on values of "+" and "-", SAS would attempt to convert both values to CON20__ since it can't use CON20_+ and CON20_-.  You would need to preprocess the data using statements that assign letters/numbers/underscores, such as: if CON20='+' then CON20='plus'; else if CON20='-' then CON20='minus'; At this point, it appears the ball is in your court.  Given the results so far, what are the final specifications? Good luck. ... View more

PGStats, I like the way you use N instead of my clumsier VAR1_FLAG.  Note, however, that there is a method to my madness.  If the solution were expanded to address some of the other questions, such as setting flags or deleting records, the subsequent programming would be different.  I'm going to imagine that I created N like you did, instead of creating VAR1_FLAG.  In that case, you would merge your result with the original data, while I would add another DO loop at the bottom of the same DATA step: do until (last.store);     set have;     by store;     * Use previously calculated variables to assign flags, and/or output proper records; end; To each his own! ... View more

Alisa, Unfortunately, you may be getting the wrong answer.  For example, assuming that 1020 is a valid customer type, but 102 is not valid, try running the macro: %listing (102) The problem is that the %INDEX function looks for strings of characters "102" not for words.  I think there is an INDEXW function, but you would have to check that, and would have to add %sysfunc to use it.  Or, you could correct my solution by switching to: strip(put(customer_type_id, 10.)) instead of: trim(customer_type_id) Good luck. ... View more

Alisa, It's good practice to add delimiters, as you have, when using %INDEX.  But you have to prepare the variables properly to produce matches.  For example, try: select trim(Customer_Type_ID) into : IDLIST separated by '*' That should do it unless there's something else that I missed. Good luck. ... View more

HB, When it comes to the missing values, PROC TRANSPOSE is the culprit.  It shifts the valid values up, replacing missing values. If you could print out about 50 rows from have4, there should be a way to get you what you need here (although from my personal perspective, it might have to wait until tomorrow).  When you post, please note whether the numbers seem correct to you and in the proper place. Good luck. ... View more