topic Re: Passing a macro varaible into Proc sql :into statement and them chekcing it with a put in SAS Programming

Passing a macro varaible into Proc sql :into statement and them chekcing it with a put

Kc2 — Mon, 11 Jun 2018 20:38:07 GMT

Hello,

I have this code;

proc sql noprint;

select count( distinct TRTN) into:endsubj from subject;

%put &endsubj;

select count (distinct TRT01AN) into:endevt from events;

%put &endevt;

select count(*) into:s1-:s%trim(&&endsubj) from subject group by TRTN;

%put &s1 &s%trim(&&&endsubj) ;

select count(*) into:e1-:e%trim(&&endevt) from events group by TRTN;

%put &s1 &s%trim(&&&endevt) ;

quit;

I get the message for the code in red : WARNING: Apparent symbolic reference S not resolved.

2 &s& 4

1)Does anyone have a suggestion as to how I should change the syntax to actually the values from S1 to S4 in this case?

In a subsequent step I merge the subject and the events datasets to get this the subjects and events by treatment group. into columns

The dataset columns looks like this .

s1 e1 s2 e2 s3 e3 s4 e4

Depending on the filters used the number of column can increase to 6 (s5 e5 s6 e6).

My final dataset needs to have all the columns named from f1 to fX.

2)How can I dynamically do a renaming or assigning of the s and e variables in the final data set:

f1=s1 f2=e1, f3=s2, f4=e2 , f5=s3, f6=e3 etc...

thx.

Re: Passing a macro varaible into Proc sql :into statement and them chekcing it with a put

SuryaKiran — Mon, 11 Jun 2018 21:06:42 GMT

proc sql noprint;

select count( distinct TRTN) into:endsubj from subject;
%put &endsubj;
select count (distinct TRT01AN) into:endevt from events;
%put &endevt;
select count(*) into:s1-:s%trim(&endsubj) from subject group by TRTN;
%put &s1 &&s%trim(&endsubj) ;
select count(*) into:e1-:e%trim(&endevt) from events group by TRTN;
%put &s1 &&s%trim(&endevt) ;

quit;

Your not referencing the macro variables using the ampersand in the right way.

&s%trim(&&&endsubj) --> this will be resolved to (&s)&(&endsubj) the paranthesis values need to be resolved first, there is no macro variable &s referenced so it trowed you error. When you change this to &&s&endsubj then it will resolve (&&s)(&endsubj)

Re: Passing a macro varaible into Proc sql :into statement and them chekcing it with a put

Tom — Mon, 11 Jun 2018 21:00:26 GMT

Don't do that.

SAS does not need to be told how many macro variables to make. It makes enough to match the data.

If you need the counts for something else then save the number observations your query generated.

proc sql noprint;
select count(*) into :s1- from subject group by TRTN;
%let subject_treatments=&sqlobs;

select count(*) into :e1- from events group by TRTN;
%let event_treatments=&sqlobs;

quit;

If you are running a really OOOOOLD version of SAS that does not support that syntax then just some value larger than you could ever need for the upper bound. SAS will still only create the number of macro variables that match the number of levels of TRTN.

select count(*) into :s1-:s10000 from subject group by TRTN;

Re: Passing a macro varaible into Proc sql :into statement and them chekcing it with a put

Astounding — Mon, 11 Jun 2018 21:08:02 GMT

Modify pretty much all references to your macro variables. Where you have this one:

%put &endsubj;

Add another statement first:

%let endsubj = &endsubj;

%put &endsubj;

Similarly, change this one:

%put &endevt;

Add a similar statement:

%let endevt = &endevt;

%put &endevt;

Then when you use these variables:

into : s1-:s%trim(&&endsubj)

Change that to:

into : s1-:s&endsubj

Next, change this one:

%put &s1 &s%trim(&&&endsubj);

Change that to:

%put &s1 &&s&endsubj;

Finally, change this one:

into : e1-:e&trim(&&endevt)

Change it to:

into : e1-e&endevt

Finally, change the final %PUT statement:

%put &s1 &s%trim(&&&endevt);

It becomes:

%put &e1 &&e&endevt;

Turn on options so you begin to get a feel for what is happening here:

options mprint symbolgen;

Re: Passing a macro varaible into Proc sql :into statement and them chekcing it with a put

Kc2 — Tue, 12 Jun 2018 00:49:58 GMT

Thanks Tom,

it solves question 1 .

But I am still stuck for question 2.

Now that I have the values assigned to macro variables macro variable, I want to rename them dynamically.

Question 2

In a subsequent step I merge the subject and the events datasets to get this the subjects and events by treatment group into columns

The dataset columns looks like this .

s1 e1 s2 e2 s3 e3 s4 e4

Depending on the filters used the number of column can increase to 6 (s5 e5 s6 e6).

My final dataset needs to have all the columns named from f1 to fX.

2)How can I dynamically do a renaming or assigning of the s and e variables in the final data set:

f1=s1 f2=e1, f3=s2, f4=e2 , f5=s3, f6=e3 etc...

Re: Passing a macro varaible into Proc sql :into statement and them chekcing it with a put

Tom — Tue, 12 Jun 2018 02:10:44 GMT

@Kc2 wrote:

Thanks Tom,

it solves question 1 .

But I am still stuck for question 2.

Now that I have the values assigned to macro variables macro variable, I want to rename them dynamically.

Question 2

In a subsequent step I merge the subject and the events datasets to get this the subjects and events by treatment group into columns

The dataset columns looks like this .

s1 e1 s2 e2 s3 e3 s4 e4

Depending on the filters used the number of column can increase to 6 (s5 e5 s6 e6).

My final dataset needs to have all the columns named from f1 to fX.

2)How can I dynamically do a renaming or assigning of the s and e variables in the final data set:

f1=s1 f2=e1, f3=s2, f4=e2 , f5=s3, f6=e3 etc...

If you need datasets then do not generate macro variables at all.

proc sql noprint;
create table subject_count as
select trtn,count(*) as n_subjects
from subjects
group by 1
order by 1
;
create table event_count as
select trtn,count(*) as n_events
from events
group by 1
order by 1
;
quit;

If you need the counts into multiple variables instead of observations then use PROC TRANSPOSE.

But do you even need a dataset with the counts in columns? Or are are you just talking about a report your need to produce?

Re: Passing a macro varaible into Proc sql :into statement and them chekcing it with a put

Kc2 — Tue, 12 Jun 2018 11:41:38 GMT

it is for a report. Once I have the columns in a certain order I need to do some processing and them generate a report.