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;
... View more
