BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kc2
Quartz | Level 8 Kc2
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

6 REPLIES 6
SuryaKiran
Meteorite | Level 14
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
Tom
Super User Tom
Super User

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;
Kc2
Quartz | Level 8 Kc2
Quartz | Level 8

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

Tom
Super User Tom
Super User

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

 

 

Kc2
Quartz | Level 8 Kc2
Quartz | Level 8

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.

Astounding
PROC Star

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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