DATA Step, Macro, Functions and more

How do i generalize a sphagetti sas code?

Reply
Contributor
Posts: 30

How do i generalize a sphagetti sas code?

[ Edited ]

Hi

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!

 

Super User
Super User
Posts: 8,281

Re: How do i generalize a sphagetti sas code?

Posted in reply to swathi123

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.

Contributor
Posts: 30

Re: How do i generalize a sphagetti sas code?

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.. 

Super User
Posts: 24,015

Re: How do i generalize a sphagetti sas code?

Posted in reply to swathi123
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.
Contributor
Posts: 30

Re: How do i generalize a sphagetti sas code?

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.. ?

Super User
Posts: 24,015

Re: How do i generalize a sphagetti sas code?

Posted in reply to swathi123

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. 

Ask a Question
Discussion stats
  • 5 replies
  • 288 views
  • 0 likes
  • 3 in conversation