DATA Step, Macro, Functions and more

Identifying the First or Last Observation in a Group

Reply
Contributor
Posts: 39

Identifying the First or Last Observation in a Group

[ Edited ]

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.

Super User
Posts: 11,343

Re: Identifying the First or Last Observation in a Group

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;
Super User
Posts: 19,850

Re: Identifying the First or Last Observation in a Group

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

Contributor
Posts: 39

Re: Identifying the First or Last Observation in a Group

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.

DateRegionWarehouse Modelstock QTY
7/26/2017R 1w1m22
7/26/2017R 1w2m21
7/26/2017R 1w2m31
7/27/2017R 2w3m12
7/27/2017R 2w3m23
7/27/2017R 2w4m11
7/27/2017R 2w4m21

Thanks beforehand.

Super User
Posts: 19,850

Re: Identifying the First or Last Observation in a Group

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;
Occasional Contributor
Posts: 11

Re: Identifying the First or Last Observation in a Group

  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);

 

sas1.JPG

 

Super Contributor
Posts: 345

Re: Identifying the First or Last Observation in a Group

Posted in reply to subobscure

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);

 

sas1.JPG

 



 

Occasional Contributor
Posts: 11

Re: Identifying the First or Last Observation in a Group

Posted in reply to andreas_lds

Super Contributor
Posts: 345

Re: Identifying the First or Last Observation in a Group

Posted in reply to subobscure

subobscure wrote:


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.

Super User
Posts: 19,850

Re: Identifying the First or Last Observation in a Group

Posted in reply to subobscure

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

 

Contributor
Posts: 39

Re: Identifying the First or Last Observation in a Group

[ Edited ]

Hello.

Many thanks for such discussion. This is not a homework Smiley Happy , 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 

 

 

Ask a Question
Discussion stats
  • 10 replies
  • 221 views
  • 2 likes
  • 5 in conversation