What I would like to see is (in one column from the output: These are defined in the datastep after the output dataset from proc tabulate. The values for Sex and Age_Group apply a character format to display as male, age 18-64, etc. In the resulting dataset from proc tabulate, the columns (variables) would have: For example, the proc tabulate would calculate Institution > Sex > Age_Group. Then, the data are nested within Institution. Let's say I have a column with a variable named group, and the value is Institution. Since I have to do this for three different top level analyses for multiple institutions, I am using a macro for the three different top levels. If your formulas do contain such references, when you replace B2, Excel will blindly put the B2 formula where the characters B2 appear in B20.Hello, using SAS 9.4, I am using a nested analysis from proc tabulate, output into a temp dataset (data were originally calculated with a nested sum function in proc tabulate I then used a datastep to perform some other arithmetic functions), then using proc print to output the aritmetic calculations to Excel.įor my final output, I would like to have some of the nested analysis levels within one column. Gotcha: Be careful that your target formulas don't contain references that contain some other form of B2 and C2, such as B20 or C210909. Excel will remove the reference to J5 from the selected cells and replace it with the characters from J5.ĭepending on how many times the intermediate formulas are referenced in the final formula, using Find and Replace might be faster than using the copy and paste method. However, the dialog remembers the settings from the last find and replace you did earlier in the current session, so it is always worth your time to click the Options button to make sure these settings are correct.) (If you start a new Excel session, both of these settings will be correct. Make sure that Match Entire Cell Contents is unchecked.
These keyboard shortcuts edit the cell, then select everything but the equals sign. Press F2, Ctrl+Shift+Home, Shift+Right Arrow, Ctrl+C, Esc. Press Ctrl+V to paste the formula from J5 to replace the reference to J5.In the formula bar, highlight the reference to J5 Press Ctrl+C to copy these characters to the Clipboard. In the formula bar, click the mouse after the equal sign and drag to the end of the formula. Instead, by copying characters from the formula bar, you have regular text on the Clipboard and can either paste into another formula or in the Replace dialog. When you copy an entire cell, you introduce many complexities, including the problem that you cannot paste this cell into the middle of a formula or into the Replace dialog. Strategy: You can select characters in the formula bar and copy them to the Clipboard with Ctrl+C. There is a relatively easy way to get all of these formulas back into a single formula.Ĭonsider the previous example, a formula in J7 =INDEX(B2:G20,J5,J6) references two other cells J5 and J6. Strategy: Using temporary sub-formulas is a great way to figure out a somewhat complex formula.
When these formulas are all working, can I combine the logic from the intermediate formulas into a single formula? Problem: When I need to build a complex calculation, I sometimes need to build several intermediate formulas to help figure out the problem.
0 kommentar(er)
