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

Accepted Solution Solved
Reply
Contributor Kc2
Contributor
Posts: 46
Accepted Solution

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

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.

Kc


Accepted Solutions
Solution
‎06-11-2018 08:50 PM
Super User
Super User
Posts: 8,127

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

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;

View solution in original post


All Replies
Valued Guide
Posts: 598

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

[ Edited ]
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)

Thanks,
Suryakiran
Solution
‎06-11-2018 08:50 PM
Super User
Super User
Posts: 8,127

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

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;
Contributor Kc2
Contributor
Posts: 46

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

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

Super User
Super User
Posts: 8,127

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

[ Edited ]

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

 

 

Contributor Kc2
Contributor
Posts: 46

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

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.

Super User
Posts: 6,788

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

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 158 views
  • 3 likes
  • 4 in conversation