topic How do i generalize a sphagetti sas code? in SAS Programming

How do i generalize a sphagetti sas code?

swathi123 — Mon, 01 Jan 2018 00:02:36 GMT

I have a code that is very very long and extensive.

How do i generalize/ normalize and make it reusable, that can be changed easily for a slightly different purpose?

Eg: i have something like

1) proc sql;
create table NEW as
(SELECT * FROM TBL A LEFT JOIN TBLB B on A.COL1= B.COL2);
QUIT;

and a ...

few more left join proc sql subqueries with casewhen statements,

2) few retain and set stmts

DATA new ;

RETAIN a b c ;
SET old;
RUN;

3) Few infile stmts
data WORK.new;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile 'C:\DESKTP.csv' delimiter = ',' MISSOVER DSD lrecl=32767
firstobs=2 ;
informat a $3. ;
informat b $6. ;
format a $3. ;
input
a $
;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
run;

Few things that I had already tried are - using %let or macro passing parameters in several places.. but does not make sense to use %let for proc sql left join tables .. #1 above

for #3, I tried csv to import macros but couldnt apply informats and formats so had to stick to infile.. The goal here is to make it as reusable as it can be by changing a few things here and there.

I also tried splitting up the code into parts for ease of understanding and then make my final code have only these statements below:-

%include '..\part1.sas'

...

%include '..\part7.sas'

Any suggestions are welcome. Thanks!

Re: How do i generalize a sphagetti sas code?

Tom — Mon, 01 Jan 2018 01:40:18 GMT

The goal here is to make it as reusable as it can be by changing a few things here and there.

The key thing is to define the few things that might need changing and replace those with macro variables. Then perhaps you can convert your long program into a macro. That way you can just call it with the details of your current situation.

Otherwise it looks like you just want to refactor the code to make it more maintainable.

Exactly how to refactor depends on what the code is doing.

For example code with a log of CASE statements might easily be simplified by using a format or joining to a lookup table, but the exact details depend on the code.

You are right that you do NOT want to use PROC IMPORT if the goal is to run it on different input datasets. PROC IMPORT must guess at what is in the file. It does that based on the specific data it is looking at. Give it a different set of data and it could make different guesses, even if the two files actually conform to the same file specifications.

Re: How do i generalize a sphagetti sas code?

Reeza — Mon, 01 Jan 2018 06:34:54 GMT

Build macros that make sense to be generalized, but for tasks that need things changed here and there make a reference program, copy and paste it and use the modified version.

Once youve done that a few times you’ll have a handle on what can be generalized and what can’t be.

Note that this is very generic advice for a very generic question.

Re: How do i generalize a sphagetti sas code?

swathi123 — Mon, 01 Jan 2018 15:11:10 GMT

Thanks for your input. Here is my question. For proc sqls that may change for the code when used next time for a different purpose, there may be three left joins instead of one, and 2 proc sqls instead of 5. Would it make sense to generalize the dataset using a %let?

I feel like some of the code especially proc sql and infile example I had mentioned in my original question, cannot be reused.. ?

Re: How do i generalize a sphagetti sas code?

swathi123 — Mon, 01 Jan 2018 16:01:29 GMT

Hi Tom Thanks for your detailed reply. I am not trying to create a maintainable code, but my motto is to create a template for other types eg:: instead of running code tue, I may run it sunday for a different purpose. I can change macro var here to be reusable.. if i change %let it can run on diff day. I incorporated let statement here.

The tables I use may vary for my new goal, however it can leverage the same logic because it is doing slightly a different task but few portions of the code remains the same and can be reused..

Re: How do i generalize a sphagetti sas code?

Reeza — Mon, 01 Jan 2018 17:43:37 GMT

@swathi123 wrote:

Thanks for your input. Here is my question. For proc sqls that may change for the code when used next time for a different purpose, there may be three left joins instead of one, and 2 proc sqls instead of 5. Would it make sense to generalize the dataset using a %let?

I feel like some of the code especially proc sql and infile example I had mentioned in my original question, cannot be reused.. ?

In this case I don't think it does. The time to develop and make sure the macro logic is correct would be longer than the time it takes to copy the code and modify it manually. Also, SQL is harder to make into macro's especially controlling joins so IMO this doesn't make sense to generalize. I would have a template program that is well commented and can easily be modified using %LET but that's about as far as I'd go when the process can vary from 2 to 5 PROC SQL.