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

[Santt0sh]

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

[Reeza]

You sure you shouldn't be using arrays here?

[Santt0sh]

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

[Reeza]

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. 

[Santt0sh]

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,

S.

[Tom]

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

[Santt0sh]

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

[Santt0sh]

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

[Reeza]

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. 

[Santt0sh]

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

[Santt0sh]

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;

[Patrick]

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

[Santt0sh]

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

[Tom]

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;

[Santt0sh]

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.