Everything I said scales to longer tables. ... View more

SQL is not a good choice for reporting. SAS has a number of PROCs specifically designed for reporting, such as PROC PRINT, PROC REPORT and PROC TABULATE, which also allow the color yellow (or any other color) to be applied where desired, and have many other advantages over SQL when creating reports. Maxim 7 (there is a procedure for it). Maxim 10 (SQL may eat your time). Maxim 14 (use the right tool).    As long as you are being picky about having a blank space between your tables and yellow headers, you should also be picky and use proper English for the column headers and values. You ought to capitalize things like Metric, Category, Region, Sales, Net_Sales, Net_Sales_Ratio, this would make the report seem even more well done and professional. You should also use plain English whenever possible, net_sales is not an English word or phrase, but Net Sales is plain English, and it would improve the appearance of the report! Use the COMMA or COMMAX format for long integers like in your table. Use the PERCENT format for percents. Replace amnt with an actual descriptive word! Produce a report that is easy to read!   Example:   ods excel file='temp.xlsx' options(sheet_interval='NONE'); proc print data=sashelp.class style(header)=[backgroundcolor=yellow color=black] style(obsheader)=[backgroundcolor=yellow color=black]; where sex='M'; id sex/style(data)=[backgroundcolor=white color=black]; var name age height weight; run; proc print data=sashelp.class style(header)=[backgroundcolor=yellow color=black] style(obsheader)=[backgroundcolor=yellow color=black]; where sex='F'; id sex/style(data)=[backgroundcolor=white color=black]; var name age height weight; run; ods excel close;       ... View more

@StickyRoll wrote: Hi @PaigeMiller  Thanks for the Proc Freq suggestion. It works. However, may i find out on the "var" part? mad_age is from the source but is not used for the computation. The variable that I am looking at is actually "MAD_DRVEXPYR1" instead. Do i just substitute it with "MAD_DRVEXPYR1" for VAR? I am just counting rows, so it doesn't matter which numeric variable I choose to do the counting of rows. MAD_AGE would give the same answer for this data set as any other numeric variable. (Assuming there are no missing values) ... View more

Lots of answers here, depending on what "non-USA keyboard characters" really means to you.   https://communities.sas.com/t5/SAS-Programming/Detect-or-Exclude-rows-which-include-not-standard-ASCII/td-p/886866 ... View more

@StickyRoll wrote: How do i aggregate and count a variable where X < 2 using SAS Datastep?   Also, can i achieve the same thing using SAS Datastep? Probably you could do this in a DATA step, but it is a lot easier using PROC FREQ to do counting or PROC SUMMARY to compute the N in each group.   proc summary data=driver nway; where mad_drvexpyr1<2; class mad_id mad_cls mad_year; var mad_age; output out=counts(drop=_type_ _freq_) n=mad_drvexpyr1_cnt; run;   ... View more

Examples and instructions to provide us the data https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/ ... View more

@SASlearner60 wrote: I was wondering if there was a way to reference date macro variable in proc SQL like operator.   For example, I want to reference macro variable PREVDATE in like operator. Please refer code example below:   %LET PREVDATE = %sysfunc(intnx(day,%sysfunc(inputn(&sysdate9,date11.)),-2,end),); %LET PREVDATE = %sysfunc(putn(&PREVDATE,date11.)); %PUT &PREVDATE;   Proc sql; Create table XYZ as Select distinct A, B  From C Where B like '%&PREVDATE.%'; quit; In my opinion, the only way this code makes sense is if variable B is a date value stored as a character string. This should be avoided, dates should be stored as numeric SAS variables, in which case all of the macro variable code to create &PREVDATE can be greatly simplified. Make your programming easy ... store dates as valid numeric variables that contain date values. ... View more

Off the top of my head, I don't have an example of why you shouldn't begin a macro definition inside a data step. If I think of one I will let you know, or maybe the others can jump in with an example. And I never said it can't work, I said (or implied) that it is considered a poor practice and should be avoided.   It seems that you have learned all the wrong lessons here. You appear to have learned that either through brute force or sheer luck or both, you can make this macro work. This is not a good lesson to carry forward, and we have tried to steer you into a better approach, which requires neither brute force nor sheer luck. There's a reason why people like @Kurt_Bremser and @Astounding (and me) recommend a different approach, and this is because it is less effort and more likely to succeed.    So, it's not that your way will never work; it's that it is inefficient and has a higher probability of failure. ... View more

I want  also to have bigger fonts in program code.   Hint: Google (and Bing and DuckDuckGo and other search engines) will find the answer immediately. Maxim 6. ... View more

I said  "Define the macro outside (before) the data step and then call it within the data step." So, %MACRO all the way down to %MEND is before the data step.   Why? Because you have to define a macro (starting with %MACRO and then some code and ending with %MEND) before you can call it. Then you can do this:   data need; set have; %var_list run;   In this way, the macro code and the data step code will work together properly, if the macro %VAR_LIST is written properly. You can't combine SAS tools (like macros and data steps) willy-nilly without regards to how they work, and expect them to work. What does happen when you execute macro code? It replaces the macro with the code inside the macro, and you CANNOT do things that are not allowed in a data step, here is an example of something that is not allowed in a data step and so won't work, even if it is called by a macro.   data want; set have; twas brillig = and the slithy toves; run;     This won't work because you cannot have spaces between variables named TWAS BRILLIG on the left side of the equal sign, you can't even have two variable names on the left side; and similarly the space on the right side will cause an error.   But again, I consider it mandatory that you write code that works and does what you want without macros and without macro variables for one instance of this problem you are trying to solve. MANDATORY. You will never make any progress otherwise. Now three people have told you to do this, I expect you will give that a try rather than continuing to attempt to write macros without that step. ... View more

Why? Does it matter? You can't do it in MIXED, so the solution is to run MIXED several times.   If you really need to know why, perhaps @Rick_SAS or @StatDave has an explanation.   "The mixed model is not more sophisticated" -- again, does it matter, MIXED does what it does.   And actually it is with respect to estimating the effect of the X-variables, a lot more sophisticated models can be fit with MIXED than you can fit with GLM. ... View more

Okay, problem #1: Do not define a macro beginning with %MACRO and ending with %MEND inside a data step. Define the macro outside (before) the data step and then call it within the data step.   Problem #2, and agreeing with @Astounding , you absolutely (as in MUST, not optional) get the code to work properly in one situation, without macros and without macro variables. If the code doesn't do what you want without macros and without macro variables, then you will NEVER be able to get it to work with macros and with macro variables.  So do that, if you are having trouble doing this, show us the code you tried that doesn't work properly WITHOUT macros and WITHOUT macro variables. And if you get it to work without macros and without macro variables, then we can help you turn it into a macro. ... View more

First, you have not shown us a macro, because there is no %MACRO statement. So that needs to be corrected.   When code doesn't work, saying "it doesn't work" and not providing more details is never helpful. We need to see the evidence that it doesn't work, that evidence is likely in the SAS log.   When you are trying to debug a macro, turn on the macro debugging command by running this line of code    options mprint;   and then run your macro again, and look at the log. See if you can figure out from the log where the problem is and how to fix it. If you still need help, show us the ENTIRE log by copying the log as text and pasting it into the window that appears when you click on the </> icon. DO NOT SKIP THIS STEP.    Why is the title "macro within macro" when your code doesn't show anything like that? ... View more

I am going to be using factor scores based on survey scores as dependent variables. In the past, when I’ve used them as covariates, I have done some imputation to retain observations with missing values. Now I am looking to revisit my measurement framework, and without imputing, I need to balance the inclusion of more variables with the loss of observations. So, I am running these percentages on various combinations of variables, and I have seven years of data. You might want to consider that SAS does have methods to handle this issue of different numbers of missings in different observations. And then all these complications of writing macros and counting the number of missings is unnecessary.   In particular, PROC PLS has a very sophisticated approach. Even if you don't want a PLS model, you want some other sort of model, this method will fill in the missings according to the EM algorithm, and you can work with this new data set that has no missings in whatever model you want.   From the PROC PLS documentation:   However, the MISSING= option in the PROC PLS statement provides more sophisticated ways of modeling in the presence of missing values. If you specify MISSING=AVG or MISSING=EM, then all observations in the input data set contribute to both the analysis and the OUTPUT OUT= data set. With MISSING=AVG, the fit is computed by filling in missing values with the average of the nonmissing values for the corresponding variable. With MISSING=EM, the procedure first computes the model with MISSING=AVG, then fills in missing values with their predicted values based on that model and computes the model again. Alternatively, you can specify MISSING=EM(MAXITER=n) with a large value of n in order to perform this imputation/fit loop until convergence. ... View more