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

I am using macro function to select certain terms to use in the following programs, and use "%put" function to check whether I have selected correct terms. All I want to select is 201205 (i.e., 2nd row under TERM column) through 202101 (i.e., 28th row under TERM column). However, the output shows that terms selected are 201201-202008. How should I change the program to make correct selection? Here I attached a screen shot of the table I used. Thanks for any feedback!

 

xliu1_0-1627580065004.png

 

PROC SQL ; *noprint;
	SELECT	
		TERM
	INTO :Term2 - :Term28
	FROM TERM_REF;

%put &Term2-&Term28;

QUIT;

xliu1_1-1627581461602.png

1 ACCEPTED SOLUTION

Accepted Solutions
jimbarbour
Meteorite | Level 14

If you prefer SQL, try this:

PROC SQL ; *noprint;
	SELECT	
		TERM
	INTO :Term2 - :Term28
	FROM TERM_REF
	WHERE 201205 <= Term <= 202101
	;

%put &Term2-&Term28;

QUIT;

Jim

View solution in original post

5 REPLIES 5
Rydhm
Obsidian | Level 7

You can use _n_ to track the row numbers first. And then use proc sql.

 

data have;
do term=201201 to 201230;
 output;
end;
run;

data keep1;
 set have;
 row=_n_;
run;

proc sql noprint;
select term into: term2 from keep1 where row=2;
select term into: term28 from keep1 where row=28;
quit;

%put &term2;
%put &term28;

If you have more rows then you can automate it using macros. But assuming you need only 2 rows, this is the simplest way.

Tom
Super User Tom
Super User

Depends on WHY you want to start with the second observation.

Do you always want to skip the first observation?  Then just exclude it from the input to your SELECT statement.

FROM TERM_REF(firstobs=2)

Or do you want to limit by the values you mentioned instead?  Are those dates with format that only displays 6 digits?  Or are the numbers like 201,201?

where term_ref >= 201205

 

jimbarbour
Meteorite | Level 14

If you prefer SQL, try this:

PROC SQL ; *noprint;
	SELECT	
		TERM
	INTO :Term2 - :Term28
	FROM TERM_REF
	WHERE 201205 <= Term <= 202101
	;

%put &Term2-&Term28;

QUIT;

Jim

jimbarbour
Meteorite | Level 14

You're welcome.

 

Incidentally, the code could be made more flexible like this:

%LET	Start			=	201205;
%LET	Stop			=	202101;

PROC SQL ; *noprint;
	SELECT	
		COUNT(TERM)	AS	Terms_Retreived
		INTO	:Terms_Retrieved	TRIMMED
		FROM TERM_REF
		WHERE &Start <= Term <= &Stop
		;
	SELECT	
		TERM
		INTO :Term1 -
		FROM TERM_REF
		WHERE &Start <= Term <= &Stop
		;
QUIT;

%IF	%BQUOTE(&Terms_Retrieved)	%THEN
	%DO;
		%put	NOTE:  &Term1-&&Term&Terms_Retrieved;
	%END;

With the above code, you don't need to know in advance how many terms (or which terms) will be retrieved.  SAS will figure out how many terms are in the range you specify via the %LET statements, create the appropriate number of macro variables, and display the range in the log for you.

 

Jim

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 898 views
  • 2 likes
  • 4 in conversation