topic Identifying the First or Last Observation in a Group in SAS Programming

Identifying the First or Last Observation in a Group

Garik — Tue, 25 Jul 2017 19:08:41 GMT

hello. I have such kind of question. I must write a macro which counts the observations, but it shows errors.

options symbolgen mlogic mprint;

%macro report;
	data mydata;
		set rep;
		 by Date;
		 
		 %put Date = first.Date =;
		%if last.Date;
		
	run;		
%mend report;

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 SYMBOLGEN:  Macro variable _SASWSTEMP_ resolves to /folders/myfolders/.images/ea76ab45-866e-41c7-8f9f-46330bb87f1e
 SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
 SYMBOLGEN:  Macro variable GRAPHINIT resolves to  
 61         
 62         %macro report;
 63         data mydata;
 64         set rep;
 65          by Date;
 66         
 67          %put Date = first.Date =;
 68         %if last.Date;
 ERROR: Expected %THEN statement not found.
 ERROR: A dummy macro will be compiled.
 69         
 70         run;
 71         %mend report;
 72         
 73         
 74         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 SYMBOLGEN:  Macro variable GRAPHTERM resolves to  
 87

it works if I run it as not macro.

Re: Identifying the First or Last Observation in a Group

ballardw — Tue, 25 Jul 2017 19:24:18 GMT

Macro instructions or function, those that start with % such as %if are expecting to manipulate macro variables or plain text. They do not "see" data step variables.

Before attempting to start macro coding you should have basic code that works for one example. Note that By processing will generally require sorting before doing something with first or last processing.

See this example for a similar task:

Proc sort data=sashelp.class out=work.class;
   by sex;
run;

data count;
   set work.class;
   by sex;
   if first.sex then count=0;
   count+1;
   if last.sex then output;
run;

Re: Identifying the First or Last Observation in a Group

Reeza — Tue, 25 Jul 2017 21:22:17 GMT

Why are you counting in a data step? Use PROC FREQ or PROC MEAN.

Re: Identifying the First or Last Observation in a Group

Garik — Wed, 26 Jul 2017 11:54:21 GMT

Dear, Reeza.

The problem is to count the stock quantity by Date by Regions by Warehouse, by models. the data is huge about 1 mln rows. there are meny regions and warehouses, and the several months and models.

Date	Region	Warehouse	Model	stock QTY
7/26/2017	R 1	w1	m2	2
7/26/2017	R 1	w2	m2	1
7/26/2017	R 1	w2	m3	1
7/27/2017	R 2	w3	m1	2
7/27/2017	R 2	w3	m2	3
7/27/2017	R 2	w4	m1	1
7/27/2017	R 2	w4	m2	1

Thanks beforehand.

Re: Identifying the First or Last Observation in a Group

Reeza — Fri, 28 Jul 2017 00:02:55 GMT

These are all reasons for using PROC MEANS or FREQ instead of a data step and most definitely not a macro. If you'd like help with how to write a PROC MEANS to solve your problem I'm happy to help with that. A macro is either homework, inexperience or job security via obfuscation.

If you have various levels levels look at the WAYS and TYPES statements to control the levels of analysis. Here's an example of how this can work at a region level.

Proc means data = have NOPRINT;
By region;
Output out = summary_region sum(stock_quantity) = total_quantity n(stock_quantity) = num_records;
Run;

Proc print data = summary_region;
Run;

Re: Identifying the First or Last Observation in a Group

subobscure — Fri, 28 Jul 2017 10:01:38 GMT

Here I have written a macro which takes the variable, you want the total for as input.
It creates the new dataset with the name of B_(the variable you entered).
This new dataset contains the total quantity by that varibale.
NOTE: For month pass m1 to the macro alpha
The data set contains the last sorted row (for model number, date, etc)

%macro alpha(var=);
proc sort data =a;
by _all_;
run;

data b_&var;
set a;
by _all_;
m1=month(input(date1,mmddyy10.));

if first.&var then total=stock_QTY; else total+stock_QTY;
if last.&var then output;
run;

%mend;

%alpha(var=date1);

Re: Identifying the First or Last Observation in a Group

andreas_lds — Fri, 28 Jul 2017 11:11:08 GMT

Well, why?

A macro is not required to solve the issue, as far as i understood it.

@subobscure wrote:

Here I have written a macro which takes the variable, you want the total for as input.

It creates the new dataset with the name of B_(the variable you entered).

This new dataset contains the total quantity by that varibale.

NOTE: For month pass m1 to the macro alpha

The data set contains the last sorted row (for model number, date, etc)
%macro alpha(var=);
proc sort data =a;
by _all_;
run;

data b_&var;
set a;
by _all_;
m1=month(input(date1,mmddyy10.));

if first.&var then total=stock_QTY; else total+stock_QTY;
if last.&var then output;
run;

%mend;

%alpha(var=date1);

Re: Identifying the First or Last Observation in a Group

subobscure — Fri, 28 Jul 2017 11:39:09 GMT

@andreas_lds Kindly go through the question posted by @Garik . The question says: " I must write a macro which counts the observations, but it shows errors ............"

Re: Identifying the First or Last Observation in a Group

andreas_lds — Fri, 28 Jul 2017 12:18:43 GMT

@subobscure wrote:

@andreas_lds Kindly go through the question posted by @Garik . The question says: " I must write a macro which counts the observations, but it shows errors ............"

I read the question, but just because the auhor thinks that a macro is required to solve an issue, does not mean that a macro is necessary (or useful) at all. The errors made in the posted code lead to the assumption that the author has not understood the difference between macro code and normal sas code.

In his second post @Garik said "The problem is to count the stock quantity by Date by Regions by Warehouse, by models." - depending on the interpretation of the listed variables this is either one proc summary or one proc summary for each class variable.

Re: Identifying the First or Last Observation in a Group

Reeza — Fri, 28 Jul 2017 15:10:17 GMT

@subobscure choose to answer the question asked. At the end of the day, that's their decision. It may not be the optimal solution, and in my experience I don't answer these types of question because if:

1. It's homework. IMO doing someone else's homework is not a good idea, both in the short and long terms since it means you don't learn what you're supposed to learn.

2. It's not the best way - it's not worth my time to show an inefficient method.

3. Because they have to 'spec' coding for clients is wrong in my opinion. I know as contractors the answer when a client asks for something is 'yes' but I strongly believe in working with clients for optimal solutions.

However, these are my PERSONAL rules. No one else is obligated to follow these conventions and s/he can answer the question as desired, with whatever type of solution they want.

Re: Identifying the First or Last Observation in a Group

Garik — Fri, 28 Jul 2017 17:54:40 GMT

Hello.

Many thanks for such discussion. This is not a homework 🙂 , this is only a part of a project which runs proc pareto, defines the the classes of goods and finally counts the stocks of the approoprite models in warehouses and adds them to the main ABC-pareto report respectively. As it is a report for logistics , it should be generated every day, and sometimes for the last periods(a day or several days), that is why I supposed a macro should be written.

so, the method that Reeza suggests works for counting the appropriate stocks.

Many thanks. 🙂