UNTESTED   columns seq service table ("Component ("Active" active active_pct) ("Reserve" reserve reserve_pct));   How can you learn data manipulation? I don't have a complete set of instructions. The main thing is that you do lots of it. No one who is new at this is going to be able to manipulate data as well as @ballardw and I (well, at least as well as @ballardw ). Here are some other things that I see are important, based upon what I see among the younger data analysts where I work. Please note: THESE ARE MY OPINIONS.   Learn to get really really good at DATA steps, PROC SUMMARY and either PROC REPORT or PROC TABULATE. These are your main calculation engines and display engines. This doesn't mean that you can't use other PROCs, but those should be secondary to the above, and use them when the above can't get you where you are going. Do not use PROC SQL as a data analysis engine. The above PROCs are simply much more powerful. Just because you can do a lot of data manipulation in PROC SQL, that doesn't mean you SHOULD do a lot of data manipulation in SQL. SQL is really good at extracting from databases, and joining tables, and similar operations, that is when you should use SQL. It also can do some things easily that DATA steps can't do as easily. However, SQL is inefficient for doing lots of calculations and creating reports and will require a lot of code; and it will run more slowly. Where I am, I see the younger people write code which has SQL after SQL for dozens/hundreds of lines; the same analysis in PROC SUMMARY would run faster and take less code. Example that I saw today (inefficient): proc sql; create table one as select mean(myvariable) from dataset; quit; proc sql; create table two as select mean(myvariable) from dataset where product='ABC'; quit; proc sql; create table three as select mean(myvariable) from dataset where product='DEF'; quit; data stats;     set one two three; run; Example: (less inefficient) proc sql; create table one as select mean(myvariable) from dataset; quit; proc sql; create table two as select product,mean(myvariable) from dataset group by product; quit; data stats; set one two; run; Example: (efficient) proc summary data=dataset; class product; var myvariable; output out=stats mean=; quit; There's actually more code needed to make the above (inefficent) and (less inefficient) code work properly, I left it out because the point I am making should be clear. You get the same numbers as a result, but the inefficient requires 12 lines and the efficient requires 5. And the efficient code will execute a lot faster than the inefficient code. And if there are additional products, such as GHI and JKL and so on, you have to keep adding to your inefficient code in a repetitive fashion. The efficient code requires nothing else to handle ABC DEF GHI and JKL properly. The "less inefficient" has less typing, but still not as little as the efficient code. And it will run slower than the efficient code. If you want to do this for two or more different GROUP BY variables, then SUMMARY blows SQL out of the water.     ... View more

We don't know what HTN is. But more importantly, we need to see how your raw data is arranged. Some data set arrangements make this easy, other arrangements take a bit of extra programming to get the desired table. So please show us a portion of your actual (or fake) data in its actual arrangement, but this is important — you must provide the data as working SAS data step code (examples and instructions). ... View more

People do add numbers in front of 'Boy','Dad', etc. and I think this is a very poor practice. The resulting table with '1Boy' looks, in my opinion, unprofessional. Fortunately, it is easy to work around this so that PROC REPORT puts things in whatever order you want. Example:   /* Create a numeric value for Boy, Dad, etc. that represents the desired sequence */ data test3; set test2; if service='Boy' then seq=1; else if service='Dad' then seq=2; else if service='Girl' then seq=3; else if service='TOTAL' then seq=999; run; /* Create table with seq to determine the order */ /* Note: seq will not be displayed in the table because of NOPRINT */ proc report data=test3; columns seq service table ("Active" active active_pct) ("Reserve" reserve reserve_pct); define seq/noprint group order order=internal; define service/group "Service"; define table/group "Table"; define active/'N' format=comma10.0; define active_pct/'%' format=percent8.2; define reserve/'N' format=comma10.0; define reserve_pct/'%' format=percent8.2; run;     Note: the creation of variable seq is done when creating data set test3, but it could have been placed in the code when creating data set test2 (simplifying the code somewhat). ... View more

UNTESTED CODE (I can't program code to pull numbers out of a screen capture)   data have2; set have; if type=136 then flag=1; else flag=0; run; proc summary data=have2; class account_number; var flag; output out=maxx(drop=_:) max=; run; data want; merge have maxx; by account_number; if flag=1; run;       ... View more

I'm back, here are more answers   Question 4:    You will need to create another macro variable where the underscore is placed before the variable name, let's call this &v2u; and you will need to create another macro variable called &v3 where the check_ is placed before the variable name.   data check_match; set want; array v2 &v2; array v2u &v2u; array v3 &v3; do i=1 to dim(v2); v3(i)=(round(v2(i))-round(v2u(i)); end; run;       Question 5:   var &v2; ... View more

@SASdevAnneMarie wrote: Thank you! Why %eval function doesn't work for this macro variable : %let tx=%eval(&Montant_EU+1) ? What about this doesn't work? ... View more

I don't have time right now to answer all of your questions, but here are some answers:   Question 1: use %sysfunc(translate( )), or use the TRANSLATE function in a DATA step; but why not just tell the user to put blanks instead of commas?   Question 2: SUMMARY will not add prefixes, only suffixes which are the name of the statistic requested. You can do bulk renames using PROC SQL as you showed Question 3: use the WHERE statement in PROC SQL to exclude the variable sex ... View more

2. TestRolling - which I suspect is wrong.  It is a loop with the number of iterations = num2. It is the length of the datelist. It is not a fixed number because stocks have various history, time since being listed.  I am not sure how num and num2 interact within the macro and loop in this case.    This requires a lot more specific information, especially since we cannot run this macro to test it. Explain why you suspect it is wrong, in detail, including what you expect to happen, and what does happen. Also please share with us a portion of the data in data set &RAW that would illustrate the problem.   PS: saying "I suspect is wrong" or "it doesn't work" or any similar phrase, without explaining in detail, never gets us anywhere. From now on, explain what is wrong, telling us what the code is actually doing and telling us what the desired output is. Please don't make us ask repeatedly for this. ... View more

    data test; length serv $5 table $5 ; infile datalines dlm=',' dsd; input serv $ table $ Active Reserve ; datalines; Boy,2796,41043,7024 Boy,PHA,274220,95291 Girl,2796,3200,1231 Girl,PHA,121634,36043 Dad,2796,9915,310 TOTAL,2796,77283,11371 TOTAL,PHA,706476,195988 ; proc sort data=test; by table; run; data test2; merge test(where=(serv='TOTAL') rename=(active=active_total reserve=reserve_total)) test; by table; active_pct=active/active_total; reserve_pct=reserve/reserve_total; run; proc report data=test2; columns serv table ("Active" active active_pct) ("Reserve" reserve reserve_pct); define serv/group "Serv"; define table/group "Table"; define active/'N' format=comma10.0; define active_pct/'%' format=percent8.2; define reserve/'N' format=comma10.0; define reserve_pct/'%' format=percent8.2; run;     The values on the total line do not appear to be the total of the lines above it. So the percents don't add to 100%.   In addition, I again urge you (in fact, I insist) that you test your code by actually running it to make sure it works and gives the right answers. In this case, your code produces errors in the log. Please don't make us waste our time running code that doesn't work, or running code where the data is clearly incorrect. We're trying to help you, but you have to help us as well. ... View more

You can't use    (where=( b.serv_ord='TOTAL'))   after the letter B. This is a DATA set option, it must go after the data set name.  ... View more

This was explained in my previous post at https://communities.sas.com/t5/SAS-Programming/Using-proc-report/m-p/924148#M363762   You didn't make the changes (plural) that I suggested. You only made one change. ... View more

SPAM duplicate of thread on Reddit https://www.reddit.com/r/statistics/comments/aqx6j6/what_is_sasiml_and_how_do_i_get_it/?rdt=57144 ... View more

You can't use    (where=( b.serv_ord='TOTAL'))   after the letter B. This is a DATA set option, it must go after the data set name. And since it is a DATA set option, you want the proper syntax here, which is   (where=(serv_ord='TOTAL')) ... View more

When you join two (or more) tables in SQL, and there is a variable (in this case ACTIVE) that exists in both tables that has the exact same name in each table, you have to refer to the variable as a.ACTIVE or b.ACTIVE so that SQL knows which one you mean. Referring to the variables as ACTIVE is ambiguous, SAS doesn't know which one you mean, and so you get this error message. ... View more