@ChrisNZ Works very nicely. Your code is simple and elegant.   I read earlier but I forgot @Kurt_Bremser advice (his Maxim 11 of 52 ... that a Macro is not needed) and the result is a negative control of an experiment (wherein the positive control being someone pursuing reuse of existing code and elegance).   Lesson: Use BASE SAS first. So much is already there. Perhaps many times, one can spare reinventing the wheel. ... View more

You can try the below. Just change the OUTFILE= with the full path of the file (in which you want all the results to go). Please note this macro may not work if you have more than 255 sheets to be exported. LIBNAME is the library/folder where all your sas datasets are there.   %MACRO ExportAllDatasetsToExcel(LIBNAME, Extension="XLS"); PROC SQL NOPRINT; CREATE TABLE columns as SELECT DISTINCT(memname) AS table_name FORMAT=$30. FROM dictionary.columns WHERE libname = &LIBNAME ; quit; PROC SQL NOPRINT; SELECT COUNT(*) AS NumObs INTO :NumObs FROM columns; RUN; %DO I=1 %TO &NumObs; DATA _NULL_ ; SET Columns (FIRSTOBS=&I OBS=&I); CALL SYMPUT('TableName',TRIM(table_name)); RUN; %PUT "&TableName"; PROC EXPORT DATA=&LIBNAME..&TableName DBMS=XLSX OUTFILE="FullPathOfTheFileHere.xlsx"; SHEET="&TableName"; RUN; %END; %MEND; Please let me know if this worked for you. I am just curious. ... View more

@hashman That is a nice, succinct but very useful table. ... View more

@Xing I have a suggestion, on similar lines to the responses given by other members. Your first SQL step is likely to be the most costly. I see that you are feeding one SQL step into the other. If you are not using first SQL block anywhere else then I suggest you start with second (or may be even the third and last) SQL block and defer the ORDER BY to the third and last SQL block. I see that all you want is distinct values of enrolid, svcdate and procgrp in the end sorted by the same variables. The DISTINCT clause should work before ORDER BY and that way you will have less number of rows to sort. In summary defer the ORDER BY as late as possible.  I might be wrong here but worth a try. ... View more

Please check this post and the reply (https://communities.sas.com/t5/SAS-Procedures/ERROR-Sort-execution-failure-in-PROC-SQL/m-p/262581#M57675).   I am quoting from the referred link, in the above post.   " The SAS® SORT and SQL procedures (PROC SORT and PROC SQL) might fail with these errors: ERROR: Sort initialization failure. ERROR: Sort execution failure. Common reasons for these errors include the following: insufficient disk space available when required insufficient memory available when required insufficient UNIX user limits on system resource or disk quota incorrect setting of a SAS system option or operating system parameter " ... View more

With the code above, I got the following output.   Output I used the macro as  %CalcTotalForYear(2005, 6); As said before, I have assumed that the dataset you have is conventionally named as "have" ... View more

@Dynamike Below code has significant room for improvement but it should be able to get the job done. Assuming your dataset is called have ..else edit in the SQL statement.   DATA MasterDataSet; FORMAT Year 8. PersonCount 8.; STOP; RUN; %MACRO CalcTotalForYear(YearStart, NumYearsAhead); %DO YEAR_Num=0 %TO &NumYearsAhead; PROC SQL; CREATE TABLE TempDataSet AS SELECT &YearStart+&Year_Num AS Year, COUNT(*) AS PersonCount FROM have WHERE YEAR(Start)<=&YEARStart+&Year_Num AND YEAR(END)>=&YEARStart+&Year_Num; QUIT; DATA MasterDataSet; SET MasterDataSet TempDataSet; RUN; %END; %MEND; /* Change below for starting year and number of years ahead */ %CalcTotalForYear(2005, 7); Ultimately it should give a MasterDataSet. Please let me know if it worked. ... View more

@killiangavin I am sorry but I don't see any example data ... View more

@Srigyan Data labels on the top of graph is going to be tricky (as the rectangles become smaller and smaller). Instead you can use an option so that when you hover over the rectangle it shows all the info. Just use the code below, hover and wait for 1 second.   ods graphics on / imagemap; proc freq data=sashelp.heart order=freq ; tables smoking_status*weight_status / plots=mosaic(square ) ; run; ods graphics off; The key is "imagemap" option in ods graphics. If rather you are looking for percent info in the table then read the second row in every block. Let me know if this helped in any way. ... View more

@maya1234  Assuming you have two datasets: the given dataset named "Have" and your dataset called "MyOwn". Also assuming you want to vertically stack the datasets (a process called concatenation) and both datasets have same named columns/variables. Then using simple code as below you can concatenate them.     DATA Merged;     SET Have MyOwn; RUN;   However if you want to merge them or you want to edit the table, the answer would be different.   ... View more

An appropriate use of PROC REPORT/TABULATE/MEANS should do the job, if pivot by means Pivot tables as obtained in Excel ... else please share some example data and expected output ... View more