How do i write a dynamic Do Loop

[SVoldrich]

Where's the best resource to learn how to do a do loop/array?
I'm using SAS EG 6.1 and 7.1

My situation:

I have a dataset with the fields: Group, Identifier, ID, 2ndID, notes, and DeleteAdd.

I want to create separate tables based on the variables found in column 'Identifier' and have the table named whatever that variable is. There are 6 of them at this time, there may be more as the project grows.... which is why i want to make the program think about it and not just hard code it.

For instance...

Table 1=PORG

Table 2=Proclaim_Fee_Schedule

Table 3=MHS_FS

Table 4=TIN

Table 5=subsetTIN

Table 6=Combination

I want this code to be iterated through the various variables in column 'Identifier'

proc sql;

create table /*iterative from the 'Identifier' column*/ as

select distinct
Group
,Identifier
,ID
,'2ndID'n
,Notes
,DeleteAdd
from NewMaster
where Identifier=  /*iterative from the 'Identifier' column*/ 
;
quit;

[Jagadishkatam]

This is a untested code which can be tried

proc sort data = have out=identifier(keep=identifier);
by identifier;
run;

data _null_;
set identifier;
call execute('proc sort data=have out='||strip(identifier)||';by group;where identifier="'||strip(identifier)||'";run;');
run;

[SVoldrich]

Thanks Jag. i'll give it a try.

[andreas_lds]

First thing to do: verify that each value in "Identifier" is a valid sas-dataset-name. Another thing to note: splitting data is almost always a bad idea, because you will, most likely, need more loops to process the datasets. Using by-group-processing is recommended.

[SVoldrich]

Good catch about the valid dataset name. that one will probably be too long.

I am not following you for the rest of your comment. 
I'm not doing anything outside of what i put in my post... I'm just manually splitting the file at the moment... and i want to try to reduce the chance of errors. 

I need a total of 7 separate tabs on one output file ... but i want to have the program determine how many there actually are incase in the future there are more added or subtracted from the list.

[ballardw]

---

@SVoldrich wrote:

Good catch about the valid dataset name. that one will probably be too long.

I am not following you for the rest of your comment. 
I'm not doing anything outside of what i put in my post... I'm just manually splitting the file at the moment... and i want to try to reduce the chance of errors. 

I need a total of 7 separate tabs on one output file ... but i want to have the program determine how many there actually are incase in the future there are more added or subtracted from the list.

---

The best description would be know what you are doing with each of the 6 data sets you create in your example. If the purpose were to print the same selected variables (all the same in your example) than an equivalent would be to sort the data by the variable identifier and then you could print the 6 groups using something like this:

Proc sort data=NewMaster;
   by identifier;
run;

Proc print data=NewMaster;
   by identifier;
run;

This is what is referred to as "By Group Processing" where combinations of one or more variables define a group of records to be processed in a similar manner.

Many times we see people new to SAS unfamiliar with the BY group splitting data into multiple sets. Often followed by a question of "How can I do X, Y and Z to each of the split out data sets?" Since a method to identify the split data and then create repetitive code now needs to be written that is much more complex then leaving the data in one set and using the by group. The number of procedures that do not support by groups is very small and mostly involve those that either do not use existing data sets such as Proc Power or do things that modify the data set description itself such as Proc Datasets.

[Tom]

---

@SVoldrich wrote:

...
I need a total of 7 separate tabs on one output file ... but i want to have the program determine how many there actually are incase in the future there are more added or subtracted from the list.

---

Sounds like you want a REPORT and not datasets.  Just use PROC REPORT with a BY statement.

ods excel file='myreport.xlsx';
proc report data=have;
  by identifier ;
run;
ods excel close;

[ballardw]

and may want to add options(sheet_interval='BYGROUP') to the ODS EXCEL depending on how you are building the report.