ALmost there, you need to loop i over the range of values as: data two(drop = Y_N nam Dose); set one; by ID Vi ; retain med1-med3 dose_1-dose_3 nY_N; array med (3) $; array dose_ (3) $; if first.vi then do; do i = 1 to 3; med(i)='';dose_(i)=''; end; i = 0; nY_N = Y_N; end; i+1; med(i) = nam; dose_(i) = dose; if last.vi then output; run; ... View more

Sorry about that. With the retain in the if first.xxx set all the values to empty or missing. Otherwise the values from the last time are retained. So you wil need to loop over the array and set med(i) = ' ' ... View more

You can get more control over transposing using a datastep with arrays. Here is a small example that may help you out. data two(drop = Y_N nam Dose); set one; by ID Vi ; retain med1-med3 dose_1-dose_3 nY_N; array med (3) $; array dose_ (3) $; if first.vi then do; i = 0; nY_N = Y_N; end; i+1; med(i) = nam; dose_(i) = dose; if last.vi then output; run; ... View more

I once led a multinational team for 14 months to answer that question just for safety. Oops .... pasting 4000 pages of documentation didn't work. ... View more

SAS creates the columns in the order they are encountered. Since you have no obs for 2005 for the first accno it will be created last. You could create an obs with variable = . for 2005 then sort prior to execution. Easier is to simply specify the order to display the columns and not worry about the internal order. ... View more

Here is a simple example of creating a 2 dimensional array. %macro test; %let i = 20; %let j = 100; data one; array newv (&i, &j) %do k = 1 %to &i ; var_&k._1 - var_&k._&j %end; ; do m = 1 to &i; do i = 1 to &j ; newv(m, i) = m*i; end; end; run; %mend; %test; ... View more

"wouldn't it be possibel to assign the counter j inside the data steps to n, and then name my variables Var_&n? " NO because n is not &n Use an array to create your variables and forget about creating and using macro variables to do this. You are making a simple situation much to complex. var_(j) = assignment; will do the job. You simply do not need the macros here. ... View more

You are trying to create a variable in the middle of a datastep. Have you looked at using your macro to set up your data structure prior to reading the data? In this simple example I am using an array to create the variables prior to reading the data. %let p = 5; data one; array avar(*) var_&p - var_9 ; do j = &p to 9; avar(j-&p+1) = J + 7; end; run; Since I have the limits of the array, I am able to set up my data structure then use the datastep loop to assign them values. ... View more

The OP error is due to the fact that the %eval is compile time. At that point i is 'i' not the value of the datastep variable i. It leads to other confusion of using macro variables for program flow within a datastep when they are not needed. It is much better to use datastep variables to control loops within the datastep whenever possible. My feeling is that this all could be done without the use of macros, except for the repitition of the datastep itself. ... View more

The statement should work within a datastep providing that &j resolves to an integer. Put an statement %put &j ; right before the data step. That should tell you if it is resolving correctly. Do not expect to be able to assign and resolve a macro variable within a single datastep. You may have better luck using dataset variables for these counters and assigning the macro variables conditionally from those. Is there a reason for updating the macro variables at each itteration of your loop? Only the last loop will be assigned at the end of the datastep. I see from your first post that this is exactly what you are doing. Macros don't work that way. Message was edited by: Flip ... View more

Try the NONOTES option ... View more

You would need to give us more of your code, but it appears you are mixing compile time macro resolution with datastep values. This will not work in the context you are presenting. Give us a better idea what you are trying to do and we may be able to help. You could for instance calculate a new value such as x = &m + i; call symput('j' , put(x best.)) ... View more

Let ORACLE do the work. Run it as a passthrough. If the lookup table is in SAS create a temporary table in ORACLE and do the subsetting there like create table big as select * from connection to oracle (select ..... .. where var in (select var from temptable)); ... View more

Wouldn't it be simpler to use a subquery for your IN statement? Or an EXISTS? ... View more

Do you have any data where either of those conditions exist? Your question requires that people be present at the same time and I see no cases where that happens. At any rate (and not testing this due to limited data) proc sql; create table fast as select a.id, count(*) as fast from one a, one b where (b.arrive > a.arrive and b.serve < a.serve) group by a.id; create table slow as select a.id, count(*) as slow from one a, one b where (b.arrive < a.arrive and b.serve > a.serve) group by a.id; create table times as select a.id, s.slow, f.fast from one a left join slow s on a.id = s.id left join fast on a.id = f.id; quit; Should work. ... View more