topic Re: Checking loop number. in SAS Programming

Checking loop number.

deleted_user — Tue, 07 Apr 2009 10:00:03 GMT

In the table, TableIn, there is a variabel, Choice, that can take any of the 20 character values A - T.
Depending on the value of Choice I want to create 20 different smaller tables.
If the macro code looks something like this:

%MyMacro;

%DO i=1 %TO 20;

DATA TableOut&i.;
SET TableIn;

Pseudocode: IF Choice nr i THEN OUTPUT TableOut&i.;

RUN;

%MEND;

This is a simple description of a more complicated situation.

In my real case I REALLY WANT to use a macro with a do loop, although it's not necessary in my example.

Is there any way inside the data step to check the value of i?

Susan

Re: Checking loop number.

LinusH — Tue, 07 Apr 2009 12:21:42 GMT

Since you don't really describe the relation between Choice and how this value affects the creation of the 20 tables, it's hard to give a qualified answer. But on your direct question: yes, &i is available in the data step. So your pseudo code should work. Macro variables are evaluated at data step compile time, which will in your case since your %do-loop wraps around a complete data step.

/Linus

Re: Checking loop number.

deleted_user — Tue, 07 Apr 2009 13:05:22 GMT

i=1 corresponds to A, i=2 corresponds to B, and so on.

Q1: If I would like to name my new data sets TableOutA, TableOutB, and so on, would that be possible? Using a macrovariable inside each data step?

In data set TableOutA there should only be persons with Choice=A, and so on.

I had several variables besides the variable Choice. I would like to keep only CHOICE, IDENTITY and SALARY.

Q2: It looks like it's not possible with a (KEEP= IDENTITY CHOICE SALARY) in the OUTPUT statement.
Do I have to do the keeping (or dropping) in a separate step?

Q3: If the correspondence between i and the values of CHOICE were arbitrary would that complicate things?

As I said: This is a simplification of the real case. I have reasons to use a macro.

Susan

Re: Checking loop number.

GertNissen — Tue, 07 Apr 2009 13:28:01 GMT

perhaps you can use this as an foundation for your further work (please post your solution)
[pre]
data input;
input Choice $ value;
datalines;
A 1
B 2
C 2
D 3
E 3
F 4
; run;

%macro sqlloop(input,byvar,keep,output);
proc sql NOPRINT;
select distinct &byvar. into :_values SEPARATED by " &output."
from &input.;
quit;

data &output.&_values.;
set &input.;
keep &keep.;
select (&byvar);
%do i=1 %to %sysfunc(count(&output.&_values.,&output.));
%let var = %sysfunc(scan(&output.&_values.,&i.));
when ("%substr(&var.,%length(&output.)+1)")
output &var.;
%end;
end;
run;
%mend;

%sqlloop(input=input, byvar=Choice, keep=value, output=TableOut);
[/pre]