Identifying the First or Last Observation in a Group

[Garik]

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.

[ballardw]

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;

[Reeza]

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

[Garik]

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.

[Reeza]

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;

[subobscure]

1. Here I have written a macro which takes the variable, you want the total for as input.
2. It creates the new dataset with the name of B_(the variable you entered).
3. This new dataset contains the total quantity by that varibale. 
4. NOTE: For month pass m1 to the macro alpha
5. 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);

[andreas_lds]

Well, why?

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

---

@subobscure wrote:

1. Here I have written a macro which takes the variable, you want the total for as input.
2. It creates the new dataset with the name of B_(the variable you entered).
3. This new dataset contains the total quantity by that varibale. 
4. NOTE: For month pass m1 to the macro alpha
5. 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);

---

---

[subobscure]

@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 ............" 

[andreas_lds]

---

@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.

[Reeza]

@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. 

[Garik]

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. 🙂