proc sql into:to create macro variable through a loop

[sahoositaram555]

Hi,

I have a dataset 

data pdata; length staratum $2. time 1. Event 1.;

input staratum $ time Event;

0 0 0

0 3 42

.....

1 0 4

1 3 2

....

3 0 5 

3 3 4

....

;run;  *************pls note .... are repetitive of the same numbers********

proc sql;
select distinct(stratum)into :stratum separated by ' ' from pdata; %let stratumcount = &sqlObs.;
select min(stratum)into :min_stratum from pdata;
quit; *****trying to get the unique values from stratum and the minimum value for the usage in loops********

%macro maxtime();
%do i=&min_stratum %to &stratumcount;
proc sql;
select max(time) into: strip('macro'||%scan(&stratum,&i.)) from pdata where stratum=%scan(&stratum,&i.) and event >0;
quit;
%end;
%mend maxtime;
%maxtime();

it shows a syntax error at the strip function, anyone could guide me though,please?

[Kurt_Bremser]

Why are you still so obsessed with making your life worse by somehow using a macro loop where it is not needed?

proc summary data=pdata;
class stratum;
var time;
output out=max max()=;
run;

data _null_;
set max;
call symputx(cats('macro',stratum),time,'g');
run;

For tested code, supply example data in a data step that works by simply copy/pasting and submitting it.

And ALWAYS (I MEAN THAT!) use the "little running man" icon (right next to the one indicated in the picture) to post code:

[sahoositaram555]

Hi @Kurt_Bremser, I genuinely appreciate your advice and if you notice this time i have crossed through those hurdles where i used to stuck in my previous topics. However, can't thank you enough for helping me when i fall in to conceptual traps starting from learning call symputx. As a beginner i personally feel that more i avoid to use loops more I'm creating holes to fall back.

I did tryout your code, but the values don't match. Therefore requesting you to help me know if my shared code can be modified to give output.
%macro maxtime();
%do i=1 %to &stratumcount;
proc sql;
select max(time) into: %sysfunc(strip("macro"||%scan(&stratum,&i.))) from plotdata where stratum=%scan(&stratum,&i.) and event >0;
quit;
%end;
%mend maxtime;
%maxtime();

this error reported below
MPRINT(MAXTIME): proc sql;
NOTE: Line generated by the macro function "SYSFUNC".
26 "macro"||3
_______
22
76
MPRINT(MAXTIME): select max(time) into: "macro"||3 from pdata where stratum=3 and event >0;
ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

[smantha]

%macro maxtime();
%do i=1 %to &stratumcount;

proc sql;
select max(time) into: macro%scan(&stratum,&i.)  from plotdata where stratum=%scan(&stratum,&i.) and event >0;
quit;

%end;
%mend maxtime;
%maxtime();

[sahoositaram555]

Hi @smantha, thanks. certainly it resolves the error, but donno why the macro vars are not getting created even though i could check in the log that the references are perfectly being resolved.
Any ideas what possible reasons could be?

[smantha]

is stratum a numeric variable or character variable? do you know for your required stratum if data exists. Posting a sample of data will be helpful. Remember the scope of macro variables in this case is within the boundaries of the macro. Any code that utilizes the macro variables must be within that boundary. Else you can use a data step and call symputx where you can control the scope of a macro variable. The following is not recommended but probably works

%global %do i = 1 %to 6; MAC&i. %end; ;

prior to your macro invocation. I hard coded the value to 6 but use whatever number you need.

[ballardw]

---

@sahoositaram555 wrote:
Hi @smantha, thanks. certainly it resolves the error, but donno why the macro vars are not getting created even though i could check in the log that the references are perfectly being resolved.
Any ideas what possible reasons could be?

---

If your Stratum data set variable is created in a manner similar to the example data Staratum then the variable is Character and you can run into issues with comparisons such as in this line:

MPRINT(MAXTIME): select max(time) into: "macro"||3 from pdata where stratum=3 and event >0;

Proc SQL will complain because when Stratum, a character variable, is compared to 3 (NOT "3") then the types are of different types.

At least one of the likely issues where you think things are resolving properly. The resolved code still has to be acceptable to the basic syntax.

Large economy size hint: Show us the code that worked as intended without any macro variables.

If you can't do that then it is time to go back and get that working first.

[sahoositaram555]

Hi @ballardw,
You have guess it right. mistakenly i had written Staratum instead of Stratum,means Stratum is a character one. I've resolved that issue by modifying the code posted below. Pls check.

option mprint mlogic symbolgen;
%macro maxtime();
%do i=1 %to &stratumcount;
proc sql;
select max(time) into: macro%scan(&stratum,&i.) from plotdata where event >0 and stratum="%scan(&stratum,&i.)" ;
quit;
%end;
%mend maxtime;
%maxtime();

[Kurt_Bremser]

Post usable example data, and the expected outcome, so we can do test-driven development.

AND USE THE PROPER WINDIWS FOR POSTING CODE!

It's not rocket science, and it won't make your head explode. Promised.

[ballardw]

---

@sahoositaram555 wrote:

Hi,

I have a dataset 

data pdata; length staratum $2. time 1. Event 1.;

input staratum $ time Event;

0 0 0

0 3 42

.....

1 0 4

1 3 2

....

3 0 5 

3 3 4

....

;run;  *************pls note .... are repetitive of the same numbers********

proc sql;
select distinct(stratum)into :stratum separated by ' ' from pdata; %let stratumcount = &sqlObs.;
select min(stratum)into :min_stratum from pdata;
quit; *****trying to get the unique values from stratum and the minimum value for the usage in loops********

%macro maxtime();
%do i=&min_stratum %to &stratumcount;
proc sql;
select max(time) into: strip('macro'||%scan(&stratum,&i.)) from pdata where stratum=%scan(&stratum,&i.) and event >0;
quit;
%end;
%mend maxtime;
%maxtime();

it shows a syntax error at the strip function, anyone could guide me though,please?

---

If you want help with specific errors, such as shown in the log, the copy the code with the errors, warnings and notes from the Log and paste into a code box opened on the forum with the </> icon.

However the "into: " instruction can only be followed by valid variable name(s), NOT an expression in an attempt to make a name, and optionally the "separated by" option.

And you aren't even using the same variable names. Your data set shows "staratum" and the SQL is using stratum.