topic Re: proc sql into:to create macro variable through a loop in SAS Programming

proc sql into:to create macro variable through a loop

sahoositaram555 — Wed, 22 Jul 2020 10:59:24 GMT

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?

Re: proc sql into:to create macro variable through a loop

Kurt_Bremser — Wed, 22 Jul 2020 11:10:02 GMT

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:

Re: proc sql into:to create macro variable through a loop

sahoositaram555 — Wed, 22 Jul 2020 13:28:37 GMT

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.

Re: proc sql into:to create macro variable through a loop

smantha — Wed, 22 Jul 2020 14:20:43 GMT

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

Re: proc sql into:to create macro variable through a loop

Kurt_Bremser — Wed, 22 Jul 2020 15:07:41 GMT

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.

Re: proc sql into:to create macro variable through a loop

ballardw — Wed, 22 Jul 2020 15:08:55 GMT

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

Re: proc sql into:to create macro variable through a loop

sahoositaram555 — Wed, 22 Jul 2020 20:06:29 GMT

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?

Re: proc sql into:to create macro variable through a loop

smantha — Wed, 22 Jul 2020 20:18:20 GMT

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.

Re: proc sql into:to create macro variable through a loop

ballardw — Thu, 23 Jul 2020 00:54:29 GMT

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

Re: proc sql into:to create macro variable through a loop

sahoositaram555 — Thu, 23 Jul 2020 04:53:38 GMT

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