topic Re: Create a DATASET for the First iteration in a Do Loop. in SAS Programming

Create a DATASET for the First iteration in a Do Loop.

Santt0sh — Thu, 07 Jul 2022 19:13:02 GMT

Hi All,

I am trying to create a Data set using a Do loop in Macro program for the first iteration or at the beginning of the Iteration.

i = 1 can be 0,3,4,5, or any number.

Kindly Suggest.

Data Sales;
Name $ Product $ Sales1 Sales2 Sales3;
datalines;
aaa xxxxx 0 0 200
dfrt iiiiiiiii 03 05 65
ertttt pxxdfd 55 0 0
;

/*START OF MACRO*/
%macro dsn(t=,NME=);
%let cnt = %sysfunc(countw(&t.));
	%do i = 1 %to &cnt.;
		%let v&i. = %sysfunc(scan(&t.,&i.));
				%put &&v&i.;
			%if %trim(&&v&i.) ne 0 /*OR %trim(&&v&i.) NE %str()*/  %then %do;
				%let ite =&i.;
				%put 	valueM&i.====>>>> &&v&i.;
				%put 	SLS&i.====>>>> SLS_&i.;
/*IM TRYING TO CREATE THE DATASET WHERE SALES IS NOT 0*/
				data SLS_&ite.;    
					SLS_&ite. = &&v&i.;
				run;
			%end;
/* THE ELSE; IS ONLY PUTTING SALES 0 VALUES FOR THE PRODUCTS*/
			%else %do;
				%let ite = %sysevalf (&i. + 1);
				%put cnt&ite.===>> &&ite.; 
				%let v&ite. = %sysfunc(scan(&t.,&ite.));
				%put &&v&ite.;
				%put 	SLS&i.====>>>>SLS_&ite.;
				%put 	VALUE&i.====>>>> &&v&ite.;
			%end;	
	%end;
%mend;
%dsn(t=0 0 10, SALES1);

Re: Create a DATASET for the First iteration in a Do Loop.

Reeza — Thu, 07 Jul 2022 19:19:30 GMT

You sure you shouldn't be using arrays here?

Re: Create a DATASET for the First iteration in a Do Loop.

Santt0sh — Thu, 07 Jul 2022 19:25:56 GMT

Hi Reeza,
Thank you for your quick response!

I am not well versed with Arrays, and this program will be used across the department to measure sales, invoices and stocks etc.

Regards,
S

Re: Create a DATASET for the First iteration in a Do Loop.

Santt0sh — Thu, 07 Jul 2022 19:32:26 GMT

Hi Reeza,

Since i am not Well versed with SAS Arrays, i m trying to use SAS Macros as i need to calculate other variables and generating various reports.

Regards,

Re: Create a DATASET for the First iteration in a Do Loop.

Reeza — Thu, 07 Jul 2022 19:35:22 GMT

Here's a tutorial on using Arrays in SAS
https://stats.idre.ucla.edu/sas/seminars/sas-arrays/

Arrays are pretty basic and very likely worth the effort to learn. Much easier, cleaner and simpler than macros to work with.

Re: Create a DATASET for the First iteration in a Do Loop.

Tom — Thu, 07 Jul 2022 19:38:12 GMT

And what is the OUTPUT you expect to get for that INPUT?

Re: Create a DATASET for the First iteration in a Do Loop.

Santt0sh — Thu, 07 Jul 2022 20:51:02 GMT

Hi Reeza,

Thank you for your quick response!
I am trying to create a new dataset for the first iteration where i NE 0.
Which should be the first I NE 0

As Discussed earlier the macro will eliminate the 0's and count it will be basically I+1 when we find a 0 in the macro variables passed
Something like this for the first iteration
Data sales1;
Set &dsn;
Run;

Regards,
S

Re: Create a DATASET for the First iteration in a Do Loop.

Santt0sh — Thu, 07 Jul 2022 20:52:32 GMT

%dsn(t=0 0 10, NME= SALES1);

Re: Create a DATASET for the First iteration in a Do Loop.

Reeza — Thu, 07 Jul 2022 21:03:58 GMT

I think you're responding to Tom not me, but I have the same question.

Please explain your input, expected output and the logic, otherwise we're very likely dealing with an xy problem.

And before you write macros you should have a working non macro version, can you show that for your use case please.

Re: Create a DATASET for the First iteration in a Do Loop.

Santt0sh — Thu, 07 Jul 2022 21:18:38 GMT

I am trying to create a new dataset for the first iteration where i NE 0.
Which should be the first I NE 0

As Discussed earlier the macro will eliminate the 0's and count it will be basically I+1 when we find a 0 in the macro variables passed
Something like this for the first iteration
Data sales_&I.;
Set &nme.;
Run;
%dsn(t=0 0 10, NME= SALES1);

Copy of the dataset Sales1. Using the first dataset created i will calculating few other variables.

INPUT:

Data Sales1;
Name $ Product $ Sales1 Sales2 Sales3;
datalines;
aaa xxxxx 0 0 200
dfrt iiiiiiiii 03 05 65
ertttt pxxdfd 55 0 0
;

Required OUTPUT.
Data Sales_1;
set sales1;
run;

Regards,
S

Re: Create a DATASET for the First iteration in a Do Loop.

Santt0sh — Thu, 07 Jul 2022 21:27:36 GMT

Hi Reeza,

I have a piece of code without macro:

When i = 2;

DATA sales_1;
SET sales;
ELEsales =MAX(1-numberofpymnts,0);
RUN;
/*calculate the commision, roi any taxes paid*/
DATA sales_ELEGBLE;
SET ales_1;
IF ELEsales = 1 ;
RUN;

Re: Create a DATASET for the First iteration in a Do Loop.

Patrick — Fri, 08 Jul 2022 00:10:17 GMT

@Santt0sh

As a rule of thumb:

1. Only use SAS Macro language if you can't do it with normal Base SAS language.

2. Always first develop a working macro free script for one use case before you make things dynamic using macro code

Reading through this discussion it's still not clear to me what you have and what you need. But I believe it's very likely that no macro code will be required (which keeps things "simple").

I suggest you post:

1. Representative sample data via a fully working SAS data step (Have) - something like below:

data have;
  infile datalines truncover;
  input Name $ Product $ Sales1 Sales2 Sales3;
  datalines;
aaa xxxxx 0 0 200
dfrt iiiiiiiii 03 05 65
ertttt pxxdfd 55 0 0
;

2. Show us how the desired result based on the sample data provided needs to look like (Want)

3. Explain the business logic required to get from the Have to the Want table.

Re: Create a DATASET for the First iteration in a Do Loop.

Santt0sh — Fri, 08 Jul 2022 02:08:11 GMT

Thank you Reeza!
As we are not sure about i = , if has different values for eg.
%dsn(t=1 200 100 0 20 0 0, NME= SALES1);

Then our results will be wrong and I only want to create a dataset only for the first iteration i.e.
i = ;

I was thinking
Do loop %Do j = 1% to 1;
data sales&j.;
set &dsn.;
run;

Regards,
S

Re: Create a DATASET for the First iteration in a Do Loop.

Tom — Fri, 08 Jul 2022 02:25:03 GMT

So this first sentence does not make any sense.

I am trying to create a Data set using a Do loop in Macro program for the first iteration or at the beginning of the Iteration.

i = 1 can be 0,3,4,5, or any number.

By I=1 do you mean an ASSIGNMENT statement to set a variable named I to the value 0 ?

If so then what does 0,3,4,5 mean?

Or are you trying to mimic the behavior of the data set DO statement where you could write something like:

data want;
  do i=1,0,3,4,5 ;
     output;
  end;
run;

And create a dataset like:

If so then put the list of values as SPACE delimited, or any delimiter other than a comma, and use another variable to index into the list of values.

%let list=0 1 3 4 5;
%do index=1 to %sysfunc(countw(&list,%str( ));
  %let next=%scan(&list,&index,%str( ));
%*  other macro statements that do something with &NEXT ;
%end;

Now you could make a macro that takes LIST as an input parameter:

%mymacro(list=0 1 3 4 5)

And if the question is how to test if a macro variable's value is equal to 0 don't make it so complicated.

%if &next = 0 %then %do;
....
%end;

If you worry that the macro variable might some strange characters that cause the condition to be misunderstood by the macro processor then adds some quotes.

%if "&next" = "0" %then %do;

Or some macro quoting.

%if %superq(next) = 0 %then %do;

Re: Create a DATASET for the First iteration in a Do Loop.

Santt0sh — Fri, 08 Jul 2022 02:50:37 GMT

Hi Tom,
Thank you for your quick response.
I have followed similar steps,

Data Sales;
Name $ Product $ Sales1 Sales2 Sales3;
datalines;
aaa xxxxx 0 0 200
dfrt iiiiiiiii 03 05 65
ertttt pxxdfd 55 0 0
;

/*START OF MACRO*/
%macro dsn(t=,NME=);
%let cnt = %sysfunc(countw(&t.));
%do i = 1 %to &cnt.;
%let v&i. = %sysfunc(scan(&t.,&i.));
%put &&v&i.;
%if %trim(&&v&i.) ne 0 /*OR %trim(&&v&i.) NE %str()*/ %then %do;
%let ite =&i.;
%put valueM&i.====>>>> &&v&i.;
%put SLS&i.====>>>> SLS_&i.;
/*IM TRYING TO CREATE THE DATASET WHERE SALES IS NOT 0*/
data SLS_&ite.;
SLS_&ite. = &&v&i.;
run;
%end;
/* THE ELSE; IS ONLY PUTTING SALES 0 VALUES FOR THE PRODUCTS*/
%else %do;
%let ite = %sysevalf (&i. + 1);
%put cnt&ite.===>> &&ite.;
%let v&ite. = %sysfunc(scan(&t.,&ite.));
%put &&v&ite.;
%put SLS&i.====>>>>SLS_&ite.;
%put VALUE&i.====>>>> &&v&ite.;
%end;
%end;
%mend;
%dsn(t= 0 0 10, SALES1);

The above code ignores the 0 and i becomes i+1 when it finds a non zero.

for example %dsn(t= 0 40 14, SALES1);
MLOGIC(BTL_DETAILS): %DO loop index variable I is now 2; loop will iterate again.
SYMBOLGEN: Macro variable I resolves to 2
MLOGIC(BTL_DETAILS): %LET (variable name is V2)
SYMBOLGEN: Macro variable T resolves to 0 40 14 14
SYMBOLGEN: Macro variable I resolves to 2
MLOGIC(BTL_DETAILS): %PUT &&V&i.
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 2
SYMBOLGEN: Macro variable V2 resolves to 40
40
MLOGIC(TRIM): Beginning execution.
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 2
SYMBOLGEN: Macro variable V2 resolves to 40
MLOGIC(TRIM): Parameter VALUE has value 40
MLOGIC(TRIM): %LOCAL I
SYMBOLGEN: Macro variable VALUE resolves to 40

now 40 is value for SALE3 , if i assign the value of SALE3 to i = 1 then it will be an issue reports

hence i want to create a dataset when i value is not 0 and we are not sure how many values will be there and in which order with 0's or without 0's.

Apologies for any confusion created.

Re: Create a DATASET for the First iteration in a Do Loop.

Tom — Fri, 08 Jul 2022 04:43:25 GMT

Simplify, Simplify, Simplify

Simplify, Simplify~~, Simplify~~

Simplify~~, Simplify, Simplify~~

Start with a simple macro.

%macro dsn(t=,NME=);
%local cnt i value ;
%let cnt = %sysfunc(countw(&t.,%str( )));
%do i = 1 %to &cnt.;
  %let value = %scan(&t,&i,%str( ));
  %if &value ne 0 %then %do;
     %put &=value is not zero ;
  %end;
  %else %do;
     %put &=value is zero ;
  %end;
%end;
%mend;
%dsn(t= 0 0 10,nme=SALES1);

Results:

36   %dsn(t= 0 0 10,nme=SALES1);
VALUE=0 is zero
VALUE=0 is zero
VALUE=10 is not zero

Things to fix.

Be explicit about which macro variables are LOCAL to this macro
Be explicit about what is the delimiter for COUNTW() and %SCAN() function calls.
Do not use %SYSFUNC() to call a data step function to do something you can do with a macro function.
Never use the SAS autocall %TRIM() macro. I have never seen a valid use case for this macro and definitely not in a case like this that does not involve any character that require the use of macro quoting.
You cannot pass a value in a macro call by position if the parameter is defined in the %MACRO statement to require that it be passed by name. (you can do the reverse: pass by name a parameter this is defined to allow the value to be passed by position)
- And if you do pass any values by position they have to come before all values that are passed by name.

Now that we have a framework for looping over a list of values and testing if any of them is zero can you please explain what SAS code you are trying to use the macro to generate?

What is the purpose of the values passed in the T parameter?
What is the purpose of the value passed in the NME parameter
What is it that you want to do differently when the next T value is zero than when it is not zero?

Re: Create a DATASET for the First iteration in a Do Loop.

Kurt_Bremser — Fri, 08 Jul 2022 05:57:21 GMT

Let's make this simple.

You have a dataset looking like this:

Data Sales;
Name $ Product $ Sales1 Sales2 Sales3;
datalines;
aaa xxxxx 0 0 200
dfrt iiiiiiiii 03 05 65
ertttt pxxdfd 55 0 0
;

What do you want to get out of this? A report, a dataset, or multiple reports/datasets?

Just show the end result, then we can go looking for the tools that are best.