Desktop productivity for business analysts and programmers

Code to create loop from data within a dataset

Reply
Occasional Contributor acw
Occasional Contributor
Posts: 9

Code to create loop from data within a dataset

Hi

I'm trying to create a code module in EG that uses data from a dataset as the basis for a loop to extract data, create an output file, export the file, then move to the next item.

Pseudo code

for each d in (select distinct date from table)
create table d as (select * from table where date = d)
PROC EXPORT DATA=work.d

OUTFILE= "\\locamachine\temp\" & d

DBMS=CSV;

run;
next d

Any pointers, relevant documentation etc would be appreciated.

Tony
SAS Super FREQ
Posts: 8,819

Re: Code to create loop from data within a dataset

Hi:
If what you want is just a CSV file for all the rows with the same date, I would be very tempted to use ODS CSV in this fashion (using SASHELP.CLASS and the SEX variable instead of a date variable):
[pre]
proc sort data=sashelp.class out=class;
by sex;
run;

ods csv file='c:\temp\by1.csv' newfile=bygroup;
proc print data=class noobs;
by sex;
run;
ods csv close;
[/pre]

Assuming that your input was already in order by date, you would do something like this:
[pre]
ods csv file='c:\temp\file1.csv' newfile=bygroup;
proc print data=datefile noobs;
by date;
run;
ods csv close;
[/pre]

If your file was not in order by date, then you could precede the ODS CSV step by a PROC SORT.

Basically, if you run the SASHELP.CLASS code, you will get 2 files: BY1.CSV and BY2.CSV -- one CSV file for every unique value of SEX in SASHELP.CLASS.

There are more complicated ways to do this with macro processing and PROC EXPORT, but ODS CSV is the simplest and easiest to understand.

You can even change the order that the columns get written to the CSV file by using the VAR statement. The only thing you can't do with this approach is to change the names. NEWFILE=BYGROUP takes the first name specified in the FILE= option and increments the number at the end of the file name by 1 for every unique BY group.

So, let's say you had 4 unique Date values in your dataset -- if you started with FILE="c:\temp\file1.csv", then ODS CSV would create FILE1.CSV, FILE2.CSV, FILE3.CSV and FILE4.CSV. (or if you had FILE="c:\temp\FILE14.CSV", then ODS CSV with NEWFILE would create FILE14.CSV, FILE15.CSV, FILE16.CSV and FILE17.CSV).

If you want to control the file name (such as having the date value in the name), then you're into "Macro program" land.

For example, consider this macro...it also creates 2 CSV files from SASHELP.CLASS, but the value for SEX is put into the FILE= option of ODS CSV.
[pre]
%macro makecsv;

** find out number of unique values for the sex variable;
** and put the number in a macro variable called NUMUNIQ;
proc sql;
select count(distinct sex) into :numuniq
from sashelp.class;
quit;

**strip leading and trailing blanks from the counter;
%let numuniq = &numuniq;

** now put the distinct values for sex into numbered macro variables;
proc sql;
select distinct sex into :want1-:want&numuniq
from sashelp.class;
quit;

** show how many times we will go through the loop;
%put numuniq values = &numuniq;

** I put ODS CSV here, but you could put;
** a proc export here as well.;

** use loop to run ODS CSV for every value of of the numbered macro variables;
%do i = 1 %to &numuniq;
%put -----> This loop for:;
%put want&i = &&want&i;

ods csv file="c:\temp\File_&&want&i...csv";
proc print data=sashelp.class noobs;
where sex = "&&want&i";
run;
ods csv close;

%end;
%mend makecsv;

** now invoke macro program;
%makecsv;
run;
[/pre]

If this approach appeals, then you might consider reading the Macro documentation on how to work with numeric variables, how to use indirect reference to macro variables (&&want&i), how to concatenate macro variables together (File_&&want&i...csv), how to use a macro %DO loop, etc.

cynthia
Occasional Contributor acw
Occasional Contributor
Posts: 9

Re: Code to create loop from data within a dataset

Cynthia

Fantastic. For my immediate short term, get something working yesterday purposes, I'll take the ods approach and run with that.

Being silly, I took the macro approach and tried to do that in open code. Unfortunately it gives an error
The %DO statement is not valid in open code
Is there a looping approach that can be used this way?

I'm harassing the boss to get some training on all this - just trying to fly before I can crawl, stand, walk etc...

Tony
SAS Super FREQ
Posts: 8,819

Re: Code to create loop from data within a dataset

Hi, Tony:
There are some macro statements that must be enclosed entirely in a macro program -- and are not allowed to be used in open code. %DO is one of those statements.

My macro program was composed of 2 main portions or parts:
1) defining the macro program:
[pre]
%macro makecsv;
...ALL the macro statements that will generate my boilerplate code ...
... including creating a CSV file for every unique value of the SEX variable
... in SASHELP.CLASS...
%mend makecsv;
[/pre]

2) using or invoking the macro program -- this statement invokes the macro program:
[pre]
%makecsv;

[/pre]

Think of the SAS Macro facility as being a big typewriter. In the normal way of doing things, if I had wanted to generate 2 CSV files for SASHELP.CLASS AND if I wanted to control the name of the file being created, I would have had to type out 2 ODS CSV "sandwiches" with a different file name for each value of the SEX variable in SASHELP.CLASS. There'd be a lot in the 2 programs that would be the same and the only things that are different are probably the filename of the CSV file and the WHERE statement that does the subset.

So the macro program is like a "boilerplate" SAS code generator or like a form letter generator. I write the program and use macro variable references (&WANT1, &WANT2) to hold the values to come out of SASHELP.CLASS. One of the neat things that I can do in a Macro program is use a %DO Loop to generate the SAME code over and over and over. Very cool.

But the rules of using Macro Language statements like %IF and %DO are that those elements MUST be used inside a Macro Program. The %MACRO signals the beginning of the code that should execute when the macro is invoked and the name of the macro program comes after the %MACRO keyword -- in this case, the name of the MACRO program is MAKECSV.

Once SAS compiles the macro program, the program is stored in a macro catalog and in order to get the program out of the catalog and USE the macro program, I have to call it with a special name: %MAKECSV.

And then, once the macro program is called, the macro processor will be able to go through the macro program line by line to see what code it can send straight to the SAS compiler for execution and which code needs to have some more action from the macro processor (like filling in the values for the macro variables) before it is passed to the SAS compiler.

Taking classes or reading some of the books on the subject are a good idea. You can also Google for papers on SAS Macro Processing. One of the best papers on the subject of Macros, for beginners, is this one: http://www2.sas.com/proceedings/sugi28/056-28.pdf

Good luck in your adventures with SAS and SAS Macro programs!

cynthia
Occasional Contributor acw
Occasional Contributor
Posts: 9

Re: Code to create loop from data within a dataset

cynthia

thanks muchly for the detailed response.

I'll plow through the link and see how I go.

Tony
Ask a Question
Discussion stats
  • 4 replies
  • 388 views
  • 0 likes
  • 2 in conversation