indexing

[Jinson]

I have a file in which for each borrower there are different segments like AS,BS,GS,RS.

Due to business requirement i need to split all the AS into 1 file from main file so on for each segments before loading the same in SAS.

But there is no Common key by which i can merge all the segments for one particular borrower.

So need help in creating a code in SAS by which i can assign a unique index no to all segments for each borrower so that can merge/join them later.

eg: index_no | borrower_name | Segments

          1                abc                    BS

          1                pqr                     GS

          1                hcbj                    RS

           2               yet                      BS

i.e it should assign the same index no till it finds the next BS in the file    

[ballardw]

I am unclear on how SAS is going to split the file before loading it into SAS.

If your source data has the borrower_name such that it is actually always the same borrower then the value of borrower_name should be sufficient for any merge or join activity. If borrower_name cannot be used that way you need to provide a lot more details on how we are supposed to know which value of borrower_name is associated with other identical values of borrower_name.

Or provide a little more example start data such as multiple values of borrower_name and segment and then what the result for that data should be. For example what happens if you have multiple values of GS before you get the second BS? Your use of segment is really not clear and might actually create multiple "index" values for the same borrower:

          1                abc                    BS

          1                pqr                     GS

          1                hcbj                    RS

          2               abc                     BS

for instance.

If you have a requirement to process all the values of your segment variable together as a group there is usually no reason to do so with SAS. The concept of BY group processing is intended to do just that, process each group of values together. A quick example using a data set that you should have available in your SAS install:

Proc sort data=sashelp.class out=work.class;
   by sex age;
run;

proc means data =work.class;
   by sex age;
   var height weight;
run;

By group processing requires that the data be sorted into the group(s) you want. The above is grouped by Sex and Age values in the data and a summary of the height and weight is performed BY those variables.

A very large majority of procedures in SAS will support By groups. So you do the same thing for each group without having to write duplicate code to process multiple individual data sets. If you need to have the final results separated then a BY group reporting process would work. Notice that SAS automatically placed information before each Proc Means output table with the Sex and Age that filtered the data for that table.