Yes, but not enough information is provided.  Is family sequential, i.e. do you want any new ones added to the end, or fill gaps, or be completely distinct?  For instance, you could simply take max(family) then a datastep which assigns missing from max(family)+1 onwards.   Also, post test data in the form of a datastep, this is an essential practice in order to get good responses. ... View more

A and B are what is known in SQL as Aliases, they are references to the from statement for that particular input (which could be a dataset as in A, or a subquery as in B).   No I cannot provide code to your dataset as you have not provided it in a usable format (it should however be pretty near).  Please refer to how to post test data as this is very important to get full answers: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 ... View more

Something like: proc sql; create table want as select a.*, b.actual from have a left join (select name,count(*) as actual from have group by name) b on a.name=b.name; quit; ... View more

You can also adjust the orientation of the page: options orientation=landscape; Before your pdf call will set page to wider rather than longer so you can fit more or.  You can also adjust cell widths: proc report... ... define id / style={cellwidth=1cm}; ... run; So the cell fits the data.  You can add wrapping within cell with split characters, or adjust the font size, or put ID on a by line, or combine columns etc.  There are hundreds of ways of achieving a certain output. ... View more

Let me try again then.  In my post I asked you to provide some basic information which would help us to answer your question.  You have responded without providing any of the requested information, but asking me to spend my time typing in the text you provided in the original post, then spend more of my time writing code.  So to answer your question: "Can you give me code example from the above example I used", the answer I am afraid is no, I am very sorry, but I cannot. ... View more

Problem 1: "i have a script that works fine in normal SQL statement without macro."   Answer: Learn Base SAS, you will find coding far simpler and easier rather than trying to force it through a third party software addon.  For example, statement 1 is simply: proc sort data=hpsncp.scada_mrcc_mv out=mrcc_asset nodupkey; by substationcode substationname substationtype voltagetype; where cats(substr(datetime_,7,4),substr(datetime_,4,2)="&period."; run; Problem 2: "The reason why i put in macro now is to handle period checking whereby different period will have different column from different source table."   Answer:  Re-assess your data modelling.  If you find you are having to resort to macro programming to handle different data, then your underlying data is badly modelled.  I can't tell exactly without seeing data, but if your calling the same macro with different groups, put those groups into your data, and then just use a by <groupvariable> statement, you will find it is a lot faster, simpler coding, and more efficient. ... View more

Again:  "Post test data in the form of a datastep, and post it in the code window - its the {i} above post area. Show what the output should look like." Nobody volunteers their time here to type in test data for you. ... View more