BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bondtk
Quartz | Level 8

Hi

 

I am trying to run these do loops on this data , there are 21 variables and under each variable some credit codes apply,  my query is find the volume of these rules on these variables.

 

Three rules are : Affordability,  Character and Policy. How many application have affordability or character or policy hits.

 

Under each rule there are certain codes which determine that these rules are applied on the application.

 

I have written this code for each rule and then run proc freq to get the volumes.

 

Question 1:  Is there a better way to write do loops for these rules as I might have to add more rules later on. As there are 21 variables

to test these rules , is there any other way to combine them or not.

 

Question 2:  if I have to exclude those applications which are withdrawn,

I put where statement in the end, but when I tried to combine 02 statements then it gives me error:

like if I say where assessordecision <> "withdrawn"  and <> ' ' ;  it works if I exclude the missing value and keep it to withdrawn

only, how do I combine the statements with where.

 

code is attached as an attachment its a long one.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Maybe I should have spelled out a more complete program.  Let me take what you have done so far, and nudge it in the right direction.  Here would be a better version:

 

data tk.volume_sbos_sbl;

set sastrain.application_extract;

format affordability_refer 1.;

affordability_refer = 0 ;

 

/*Capacity rule*/

 

array aff{*} RB_postBurreasoncdetable_01-RB_postBurreasoncdetable_21;

if AppSystem in ('SBLoans') then do i=1 to 21 until (affordability_refer=1);

if aff{i} in   

('RA83','RA51','RA92','RA93','RA12','RA13','RA57','RA29') then affordability_refer= 1;

end;

run;

 

The DO loop uses UNTIL so it can stop checking the remaining items in the array, as soon as it finds a match on one item.

 

View solution in original post

10 REPLIES 10
jklaverstijn
Rhodochrosite | Level 12

It is always a good idea to suspect optimizations exist if you notice repetition in your code.

 

Without trying to do the coding itself I can give some pointers:

 

1) Variable lists with a numeric postfix (but also others) are good candidates stick in an array. You can then loop over that array. See this older but stil relevant SGF paper. It is easy to add or change your arrays at a later stage.

2) These groups of codes that map to a single flag can be handled by a format. Your "if value in ('a', 'b', ...) then" can be transfored into "flag = put(value, $mappingfmt.)". The  proc format at the top of your code would be the single place to add or change mappings.

 

Hope this helps you on your way,

-- Jan

jklaverstijn
Rhodochrosite | Level 12
An entirely different approach could be to transpose your data. If you have so many similar columns consider transforming that to a dataset with just one variable plus an extra indicator for the suffix that you would remove. The aforementioned technique with arrays and do loops could be used for the purpose of transposing if proc transpose doesn't cut it. You would then not have to repeat the mapping for each variable in each row but just the single variable in each row. This wide to long transposition usually gives you a dataset that is easier to analyze so you may benefit from this approach in other scenario's as well.

- Jan.
jklaverstijn
Rhodochrosite | Level 12

And on question 2: since your variable is character, the use of "<>" is not appropriate. Also, the variable your testing must be repeated after the AND:

 

AssessorDecision ne "Withdrawn" and AssessorDecision ne ' '

Regards,

- Jan.

Astounding
PROC Star

This may or may not run faster.  But it's a whole lot easier to write, read, and QC:

 

proc format;

invalue afford 

'RA83', 'RA51', 'RA92', 'RA93', 'RA12', 'RA13', 'RA57', 'RA29' = 1  other=0;
run;
 
array aff {*} RB_PostBurReasonCdeTable_01 - RB_PostBurReasonCdeTable_21;
 
if AppSystem in ('SBLoans') then do _n_=1 to dim(aff) until (affordability_refer=1);
   affordability_refer = input(aff{_n_}, afford.);
end;
 
Similar code needed for the other two measures.  This is untested, so may need to be tweaked.
jklaverstijn
Rhodochrosite | Level 12

Yes very much as I suggested. Kudo's for using an informat instead of a format. Wouldn't use the name _n_ for the loop index however as this is a special variable.

 

regards,

-- Jan

Astounding
PROC Star

Just a side note really ...

 

I often use _n_ as the index for an array.  The minor benefit is that I don't have to remember to drop it later.  But the major benefit occurs in this sort of code:

 

array nums {*} _numeric_;

do i=1 to dim(nums);

   * something, anything that changes nums{i};

end;

 

If by some oversight the list _NUMERIC_ includes the variable i, big trouble results.  Either the results are wrong (likely with no note or warning), or else an infinite loop results (if, for example, the loop changes nums{i}=0).  So it's a choice I make which to me is the lesser of evils.

bondtk
Quartz | Level 8

Hi

 

Thanks for your reply,  I tried the code:

 

but comes up with errors, I am expert on arrays so not sure how to fix it.

 

 

17 invalue afford

18

19 'RA83', 'RA51', 'RA92', 'RA93', 'RA12', 'RA13', 'RA57', 'RA29' = 1 other = 0;

NOTE: Informat AFFORD is already on the library.

NOTE: Informat AFFORD has been output.

19 ! .

20

19 'RA83', 'RA51', 'RA92', 'RA93', 'RA12', 'RA13', 'RA57', 'RA29' = 1 other = 0;.

_

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

21 run;

NOTE: The previous statement has been deleted.

21 ! .

22

21 run;.

_

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

23 .

24

25 array aff {*} RB_PostBurReasonCdeTable_01 - RB_PostBurReasonCdeTable_21;

NOTE: The previous statement has been deleted.

25 ! .

26

25 array aff {*} RB_PostBurReasonCdeTable_01 - RB_PostBurReasonCdeTable_21;.

_

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

27 .

28

29

30 if AppSystem in ('SBLoans') then do _n_=1 to dim(aff) until (affordability_refer=1);

NOTE: The previous statement has been deleted.

2 The SAS System 11:21 Sunday, December 3, 2017

31

32 affordability_refer = input(aff{_n_}, afford.);

___________________

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: The previous statement has been deleted.

33

34 end;

___

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

NOTE: The previous statement has been deleted.

35 run;

 

 

******************************************

 

 

I also tried this code : didn't work;

 

data tk.volume_sbos_sbl;

set sastrain.application_extract;

format affordability_refer 1.;

affordability_refer = 0 ;

 

/*Capacity rule*/

 

array aff{*} RB_postBurreasoncdetable_01-RB_postBurreasoncdetable_21;

if AppSystem in ('SBLoans') then do;

if aff{*} in ('RA83','RA51','RA92','RA93','RA12','RA13','RA57','RA29') then affordability_refer= 1;

do i = 1 to 21;

end;

end;

run;

 

Astounding
PROC Star

Maybe I should have spelled out a more complete program.  Let me take what you have done so far, and nudge it in the right direction.  Here would be a better version:

 

data tk.volume_sbos_sbl;

set sastrain.application_extract;

format affordability_refer 1.;

affordability_refer = 0 ;

 

/*Capacity rule*/

 

array aff{*} RB_postBurreasoncdetable_01-RB_postBurreasoncdetable_21;

if AppSystem in ('SBLoans') then do i=1 to 21 until (affordability_refer=1);

if aff{i} in   

('RA83','RA51','RA92','RA93','RA12','RA13','RA57','RA29') then affordability_refer= 1;

end;

run;

 

The DO loop uses UNTIL so it can stop checking the remaining items in the array, as soon as it finds a match on one item.

 

bondtk
Quartz | Level 8
Thanks Astounding, this is perfect as I wanted, it worked perfectly...it created a new column i which I removed with drop statement . All the results are good with the proc freq statement. Just one question , I have got these numbers from yearly data , so proc freq has given me the volume that how many times these rules are hit , if I want to see a trend during the whole year how these rules are hit to this data what will be the easy option to achieve that..
Astounding
PROC Star

You can only do that if you have some version of a date in the data set.  Then you could pick out the month (how you do that depends on what the date variable contains).  Give the month a value from 1 through 12, and use that as one dimension of the table in a PROC FREQ.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2237 views
  • 0 likes
  • 3 in conversation