topic Re: Do Loops are doing my head in. in SAS Procedures

Do Loops are doing my head in.

Scottcom4 — Fri, 26 Sep 2008 06:45:32 GMT

Hi Guys,

I am attempting to find and output the last record within a dataset. I know.....I know it is sooooo simple, however I am coming unstuck because I am attempting to do so where a condition is met and on some occasions I may need to output more than one record. For instance.

Data:

Code Product 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23-55 56+
069 Motor 25 182 67 217 88 39 36 5 1 40 12 8 8 7 4 0 14 7 4 1 6 1 0 61 45
070 Motor 18 114 28 11 44 21 19 16 12 3 0 9 5 11 7 6 2 1 6 6 3 2 5 52 60
071 Motor 153 102 12 6 23 12 5 4 5 0 0 1 3 1 1 1 0 1 4 1 0 1 2 0 0
072 Motor 259 786 559 74 21 91 55 63 39 21 4 1 15 16 17 12 11 1 0 5 8 8 0 0 0

In this case I want to output both code 071 and 072 to 2 dataset (Outstanding071 and Outstanding072), because both of these codes have outstanding tasks. I gather a Do Loop would do the job (although I could be wrong), but have no idea what to do and keep crashing the server (whoops).

Does anyone have any ideas how I can rectify this (please be kind).

Regards,
Scott

Re: Do Loops are doing my head in.

Cynthia_sas — Fri, 26 Sep 2008 07:45:42 GMT

Hi:
I'm not sure whether you really need a DO loop or not. Basically, the DATA step program is an implied loop -- it will repeat the statements in the program for every dataline or row in the input data.

Consider this program. It reads in data that resembles your example (without so many values), calculates the sum of the numbers on each row (excluding CODE and PRODUCT) and creates 2 output data sets, based on the value of CODE. Since I didn't know what your criteria were for deciding that something was outstanding, I just did the output based on the value of CODE:
[pre]
data testcode;
infile datalines;
input code product $ var0 var1 var2 var3 var4;
format code z3.;
return;
datalines;
069 Motor 25 182 67 217 88
070 Motor 18 114 28 11 44
071 Motor 153 102 12 6 23
072 Motor 259 786 559 74 21
;
run;

data out071 out072;
set testcode;
tot = sum(of var0-var4);
if code = 71 then output out071;
else if code = 72 then output out072;
run;

proc print data=out071;
title '071';
run;

proc print data=out072;
title '072';
run;
[/pre]

If you need to create a more dynamic program, then you may need to investigate using a SAS macro program to dynamically find out which records meet your criteria and create the appropriately named output datasets.

If you decide that you do need to use SAS Macro processing for your task, then the key thing is to start with a working SAS program. Make sure you have the program logic working in a 'regular' program before you try to introduce macro elements into the code.

For more information about the SAS Macro Facility, the documentation is quite good and this paper provides a good introduction:
http://www2.sas.com/proceedings/sugi28/056-28.pdf

cynthia

Re: Do Loops are doing my head in.

Patrick — Fri, 26 Sep 2008 14:55:38 GMT

Hi Scottcom4
Cynthia gave you already a lot of input. I was just wondering about "...to find and output the last record..."; but then you're talking about the "codes" which have nothing to do with "the last record" - and these codes are also unique in your example; so it's also not the last record of a by group (by code)???
Looking at your example I also didn't understand how you identify outstanding records.

In general:
You use loops to perform a repetitive task within an Observarion ('record') - a "line of data" to express it a bit un-IT.

If you're rather unexperience with programming then I would be careful to go into too much SAS macro programming before you understand the basics. SAS macro programming IS a very powerfull addition - but I've seen too many examples where people got lost in macro code only because they didn't understand the basics (and SAS macro coding wouldn't have been necessary at all).
I can't agree with Cynthia that the SAS Macro manual is that good (at least in comparison to the outstanding BASE SAS manual).

Have a look at the example code below! I hope this will be helpful for you.
I can only recommend you to look up and understand how "into" works together with Proc SQL - it's not too difficult to learn and very powerful.

HTH
Patrick

data have;
infile datalines truncover;
input code:$3. product:$20. var1-var25;
datalines;
069 Motor 25 182 67 217 88 39 36 5 1 40 12 8 8 7 4 0 14 7 4 1 6 1 0 61 45
070 Motor 18 114 28 11 44 21 19 16 12 3 0 9 5 11 7 6 2 1 6 6 3 2 5 52 60
071 Motor 153 102 12 6 23 12 5 4 5 0 0 1 3 1 1 1 0 1 4 1 0 1 2 0 0
072 Motor 259 786 559 74 21 91 55 63 39 21 4 1 15 16 17 12 11 1 0 5 8 8 0 0 0
;

/* output all records where two consecutive VARn values are 0 */
data OutstandingAll;
set have;
drop i;
array vars {25} var1-var25;
do i=1 to dim(vars)-1;
if vars{i}=0 and vars{i+1}=0 then
do;
output;
leave;
end;
end;
run;

/*----------------------------------------------------------------------------------------------------------------------
Now comes the more difficult part: We want to output the records to datasets where part of the
dataset-name is the value of the variable 'code' with outstanding records. The following is a dynamic solution where
you don't have to define all the possible dataset names in advance (codes might change...)
The approach is:
- Use Proc SQL with Distinct to find all Unique codes in the dataset "OutstandingAll" (=all records which are outstanding).
- Write the result into macro variables (which we can use later on in another datastep). What the code
does is concatenating text with variable values and storing them into two macro variables. We can use
these macro vars later on. They are resolved by the macro processor (which runs always before the 'Base'
SAS processor. So the SAS processor sees the resolved macro vars (have a look in the log - there you see
to what they resolve) and uses the text in the macrovars.
- The macro var &DatasetList contains all the needed names for the output tables.
- The macro var &WhenClause contains the SAS Statements to write the SAS observations to the corresponding SAS dataset

Look up "into" as part of Proc SQL. This is VERY powerful and helps avoid a lot of coding.
--------------------------------------------------------------------------------------------------------------------------*/

proc sql noprint;
select distinct cats('work.','Outstanding',code),cats('when ("',code,'") output Outstanding',code,';')
into :DatasetList separated by ' ', :WhenClause separated by ' '
from OutstandingAll;
%put &DatasetList;
%put %bquote(&WhenClause);
quit;

data _null_ &DatasetList;
set OutstandingAll;
select(code);
&WhenClause
otherwise;
end;
run;

Re: Do Loops are doing my head in.

Scottcom4 — Sun, 28 Sep 2008 22:31:31 GMT

Thank you so much to you both. You did a fantastic Job.

Re: Do Loops are doing my head in.

Scottcom4 — Mon, 29 Sep 2008 02:01:04 GMT

Thank you Message was edited by: Scottcom4