program/programming improvement

Batta — Tue, 11 Jan 2022 21:28:52 GMT

Hi SAS Experts
I apologized about the length of my “question”:
I run reports every week by pooling data from our SAS daily downloads, extracting whatever needed, performing necessary formatting, table joins, calculations, comparisons…etc. and finally creating Excel spreadsheets that data managers would use for their purposes. I mostly use “proc sql” and the way I do it is that I have a code written and saved as a word document and every week I would change/replace the date in my code, copy and paste the entire code into SAS (probably not the most fortunate way). I have about 20 libraries I create from SAS data downloads and pool different data from - maybe 10 or more conditions per library)
So the code would look something like this:
libname newlib 'Z:\SAS_downloads\Today';
run;

proc sql;
create table table1
select var1 var2
from newlib.SAS_table
..
..
..
..
..
quit;

proc export
data=table1 dbms=xlsx
outfile="\\xxxxxxx\Documents\FILES\Excel_File_date.xlsx"
replace;
sheet="Sheet_Date";
run;

Also, I compare previous reports and the current one to find new entries using proc import to import current and previous week reports and the proc sql to extract only new entries and, again, proc export to create new excel spread sheet that will be sent to data managers.
My way is working OK, but, based on number of libraries and conditions I need to run, I think I have way too many repetitions in my code (for some libraries the conditions/data I am extracting are similar, just library names are different) so I am curious if there is a way (or ways) to make the entire process more data-oriented, optimized and more sophisticated so I can avoid those unnecessary repetitions.
Thank you very much for dedicating your valuable time to these. Please let me know if you need more details as I wanted to keep this as short as possible. Any suggestion, correction, opinion is highly appreciated.
Kind regards!

Re: program/programming improvement

ballardw — Tue, 11 Jan 2022 22:06:19 GMT

First a minor comment: Storing SAS code in a WORD document, or other spreadsheet, is next to dangerous. Some of the word processing programs will replace simple programming quotes like ' with a curly quote (or apostrophe) that looks nice in print but means code will not run.

An example at : https://communities.sas.com/t5/SAS-Programming/Smart-Quotes-Vs-Simple-Quotes/m-p/396099

Also some text formatting stuff that you don't see can affect things.

SAS has function, Today() that will return the value of the current date that the statement executes. So you can test or modify that value with date functions to create other text such as the names of files and such, or to increment a base value to select values with a specific range of days(or weeks, months or years) of that value with the INTCK and/or INTNX functions.

Since you did not show any example of how you might actually use the date value it is hard to go much further than that.

If your code is static enough you might be able to use a CALL EXECUTE approach which would let you use a data set containing the main part of the code and insert the appropriate date values into the statements that would depend on the date.

Another approach is to write the code to use macro values for the date dependent bits and then the program at the top would use the Today function and other statements to set the date dependent bits. Again, it might help to show which parts of your code need to change and the rules involved.

topic program/programming improvement in SAS Programming

program/programming improvement

Re: program/programming improvement